RE: SQL grouping

[rob@xxxxxxxxx]

I tried doing this with SQL via a "pivot" table and gave up.  Again, it's 
because the value of the feature does not stay constant in a particular 
column.  I assume that you have more than 8 possible features, but no 
customer will have more than one.  (Much like no one gets all 7 sacraments 
in the Catholic Church - Priesthood vs Marriage thing.  And even there - 
there have been exceptions.)  Because if that wasn't the case, and you did 
NOT have more than 8 possible features than a pivot table would work. 
Feat1 could only be ABC, etc.

Workarounds.
1 - Normalize your database.  The excel one is actually laid out better. 
Fancy that.
2 - Use RPG or some such language using control break reporting to do it 
instead.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Lim Hock-Chai" <Lim.Hock-Chai@xxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces+rob=dekko.com@xxxxxxxxxxxx
01/13/2005 12:38 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: SQL grouping






This is an one time fix.  There are only 2 to 3 features to deal with for 
a given phone#.  I recevie the data in a spreadsheet.  However, our 
database is in the later format.  Another word, pleeease give me the 
example.



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Tony Carolla
Sent: Thursday, January 13, 2005 11:27 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL grouping


If there are a fixed, reasonable number of feature codes, and a fixed,
reasonable number of codes for any given ph#/extension, then you could
do it with UNIONS and CASE statements.  But this is probably not the
case <---- pardon the pun.

If it is the case, let me know, and I could give you an example.


On Thu, 13 Jan 2005 09:28:48 -0600, Lim Hock-Chai
<Lim.Hock-Chai@xxxxxxxxxxxxxxx> wrote:
> 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.
> 
> 


-- 
"Enter any 11-digit prime number to continue..."
-- 
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.


-- 
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.