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.

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

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].