Re: Use SQL to give me one record from many

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.