Re: SQL in iseries: how to remake a file

Yah, just tried that...MIN was a bad choice when I set the else to blanks. Try MAX instead.



________________________________
From: Mike Krebs <majkrebs@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Wednesday, March 28, 2012 12:04 AM
Subject: Re: SQL in iseries: how to remake a file

Rob's answer would look like this

select cust# ,
 min(case when code = 'A'  then 'A' else ' ' end) as CODEA,
 min(case when code = 'B'  then 'B' else ' ' end) as CODEB,

 min(case when code = 'Z'  then 'Z' else ' ' end) as CODEZ

from custcode group by cust#

There might be other ways to do the grouping but I usually use something like MIN.

I think you can create a view using the above and then it could be queried.


This is actually pretty easy to do in excel using pivot tables. In that case download the file as is and run the pivot table.


There is a utility called CRTCTSQL that Michael Sansoterra wrote that will do this for unknown or larger sets of "unknown" codes.
http://www.itjungle.com/mpo/mpo032703-story01.html



________________________________
From: Raul A. Jager W. <raul@xxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Tuesday, March 27, 2012 6:06 PM
Subject: Re: SQL in iseries: how to remake a file

If you only have 3, or a small, limited, number of values, then you can
join the table with itself, the key will appear once and the values will
be null if the record does not exist.

Stone, Joel wrote:

I have the following file:


CUST#        Code
------     -------
0001          A
5555          A
5555          Z
5555          B
6666          A
8888          A
8888          Z



Is there a simple way in SQL or QM or Query to make it look like:

Or is a pgm required?



Cust#       CodeA       CodeB        CodeZ
------     -------     -------      --------
0001          A
5555          A           B            Z
6666          A
8888          A                        Z


Assume physical files exist in the formats shown above.


End result desired is an excel spreadsheet.

What would this be called?  Pivot table? 

Thanks in advance!!






______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________