AW: SQL grouping

Hi,

there are no easy solutions.
The following example works for three instances, for eight you have to
expand it.

with first  as (select phone as phone1, PhoneExt as Ext1,
                       Min(feature) as Min1
                  from phoneno
                  group by phone, phoneExt),
     second as (select phone as phone2, PhoneExt as Ext2,
                       Min(feature) as Min2
                  from phoneno join first  on phone1 = phone
                                          and ext1   = phoneext
                                          and min1   < feature
                  group by phone, phoneExt),
     Third  as (select phone as phone3, PhoneExt as Ext3,
                       Min(feature) as Min3
                  from phoneno join second on phone2 = phone
                                          and ext2   = phoneext
                                          and min2   < feature
                  group by phone, phoneExt)
select   phone1, phone2, min1, min2, min3
   from  first left outer join second on phone1 = phone2
                                      and ext1  = Ext2
               left outer join third  on phone1 = phone3
                                      and ext1  = Ext3

But be aware, I haven't checked performance. That could be terrible.

Birgitta

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Lim Hock-Chai
Gesendet: Donnerstag, 13. Januar 2005 16:29
An: rpg400-l@xxxxxxxxxxxx
Betreff: SQL grouping


David, let me know if this should not post here.

I have a file that contains data looks something like this:

Phone#         Phone Ext#    Feature
1234567980      1234567       ABC
1234567980      1234567       DEF
1234567980      1234567       XXX
1234569999      9994567       GXX
1234569999      9994565       XXA
6019991234                    ABC


Is there a easy way to transform it to look like this using interavice SQL
if I know that the most feauture an unquie phone+ext can have is 8?

Phone#      PhoneExt#     Feat1    Feat2    Feat3    Feat4    Feat5    Feat6
Feat7    Feat8
1234567980   1234567       ABC      DEF      XXX
1234569999   9994567       GXX      XXA
6019991234                 ABC


--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.