Re: Grouping results horizontally in SQL

[Charles Wilt <charles.wilt@xxxxxxxxx>]

A pivot table isn't going to help here....

You're probably going to have to build some sort of UDF to concat the
values in the ORDER column into a single value...

Charles

On Tue, Jul 3, 2012 at 10:22 AM, Jerry C. Adams <midrange@xxxxxxxx> wrote:
> Michael Sansoterra wrote an article and included code at IT Jungle
> (http://www.itjungle.com/mpo/mpo032703-story01.html) that might do the
> trick.  I've never used it personally, but I installed Michael's stuff at my
> previous employer and my boss (who was our SQL guru) loved it.
>
> Jerry C. Adams
> IBM i Programmer/Analyst
> All things are possible except skiing through a revolving door.
> --
> A&K Wholesale
> Murfreesboro, TN
> 615-867-5070
>
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jonathan Mason
> Sent: Tuesday, July 03, 2012 8:54 AM
> To: 'midrange-l@xxxxxxxxxxxx'
> Subject: Grouping results horizontally in SQL
>
> Hi All
>
> I'm sure this must have been asked before, but I can't for the life of me
> find anything in the Archives, so my apologies if that's the case.
>
> I have an order header file with a separate record for each
> account/year/period key combination. For example
>
> Account Year  Order
> ------- ----  -------
> 1000    2012   10001
> 1000    2012   11023
> 1000    2012   12700
> 1000    2012   13508
> 2000    2012    1255
>
> Ideally I would like to see the results as shown below:
>
> Account  Year  Orders
> -------  ----  --------
> 1000     2012    10001, 11023, 12700, 13508
> 2000     2012     1255
>
> I have been looking online and can see solutions that might help, but they
> are for different flavours of SQL and use functionality that isn't present
> within our version (v6r1),of SQL.
>
> Has anybody come across a method of doing this?
>
> Thanks
>
> Jonathan
>
>
> Proud partner of The Ageas Bowl and the Ageas Salisbury International Arts
> Festival.
>
> Registered Address: Ageas House Tollgate Eastleigh Hampshire SO53 3YA
> Registered Number: 354568 England
> Authorised and regulated by the Financial Services Authority
>
> This e-mail together with any attachments are intended for the addressee
> only and may be private and confidential. If you are not the intended
> recipient, or the person responsible for delivering it to the intended
> recipient, you must not open any attachments, or copy, disclose, distribute,
> retain or use this e-mail, including any attachments, in any way whatsoever;
> please return it to us immediately using the reply facility on e-mail.
>
> Consider the environment and think before you print this email.
>
> --
> 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.
>
> --
> 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.