Re: SQL Question: Combine Several Rows Into One Column?

[Luis Rodriguez <luisro58@xxxxxxxxx>]

Would not this be a problem where recursive views could be of help? The shop
I work for is stuck at V5R3 but, at least from what I can see from the
manuals (V5R4 and on) it seems an UDF could be built around this, albeit
"with a lot of work"...

Regards,


Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries


On Wed, Apr 7, 2010 at 9:00 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

> You might be able to write a UDF that would do it....
>
> You can do a pivot...
>    Sub Type       A    B    C    D    P   Q
> Type
> 1                       X    X     X    X
> 2                                                X    X
>
> Then maybe run the pivoted table through some field concatenations...
>
> But since the i doesn't have a built-in pivot function, you have to
> have a predefined set of values in sub-set to pivot on.
>
>
> HTH,
> Charles
>
>
> On Wed, Apr 7, 2010 at 8:58 AM, Michael Naughton
> <michael_naughton@xxxxxxxxxxxx> wrote:
> > I think I know the answer to this, but I wanted to see if there's
> something I've missed. Suppose I have the following table:
> > Type    Sub-Type
> >  1          A
> >  1          B
> >  1          C
> >  1          D
> >  2          P
> >  2          Q
> >
> > Is there a way to use SQL to produce the following result:
> >
> > Type     Sub-Types
> >   1         ABCD
> >   2         PQ
> >
> > The idea is to somehow concatenate all the sub-types for a given type
> into one column, given that a type may have any number of sub-types.
> > I suspect the answer is "no", or at least "not without a lot of work". Am
> I missing something?
> > Thanks very much,
> >
> >
> >
> >
> > Mike Naughton
> > Senior Programmer/Analyst
> > Judd Wire, Inc.
> > 124 Turnpike Road
> > Turners Falls, MA  01376
> > 413-676-3144
> > Internal: x 444
> > mnaughton@xxxxxxxxxxxx
> > ****************************************
> > NOTICE: This e-mail and any files transmitted with it are confidential
> and solely for the use of the intended recipient. If you are not the
> intended recipient or the person responsible for delivering to the intended
> recipient, be advised that any use is
> > strictly prohibited. If you have received this e-mail in error, please
> notify us immediately by replying to it and then delete it from your
> computer.
> > --
> > 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.