Re: Use SQL to give me one record from many

You want to "pivot" the data...

Google pivot and iseries and you should find plenty of info.

However, it's ugly and it's hardcoded...meaning you usually have to
know all values of field2...

You might need to do a CTE first using ROW_NUMBER() to provide a
limited set of data values to pivot on...

Charles

On Tue, Jun 7, 2011 at 1:57 PM, Alan Shore <ashore@xxxxxxxx> wrote:

Hi everyone
Here is my predicament
I have one file with 2 fields, for example
Field 1                  Field 2
A                            100
A                            150
A                            161
B                            007
C                            005
C                            001
C                            017
C                            999
Etc.
I need to provide the following result in a different file
Field 1                  Field 2                  Field 3                  Field 4 etc.
A                            100                       150                       161
B                            007
C                            001                       005                       017                       999
And so on
I am sure that I have seen some e-mails about this in the past, and I have tried looking for them, but I am not even too sure what the correct key words should be
So - if anyone can point me in the correct direction, as always, all responses MUCH appreciated

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.