Re: How to flatten cols into rows

andrewst_at_onetel.net.uk (Tony) wrote in message news:<c0e3f26e.0401300424.4548e051_at_posting.google.com>...
> peteg_at_garlic.com (hedrew3) wrote in message news:<aad10be0.0401292322.7b6c320b_at_posting.google.com>...
> > I have a single table, as follows:
> >
> > Col_A Col_B
> > ------ ------
> > Joe X
> > Joe Y
> > Joe Z
> > Bob A
> > Bob B
> >
> > and I need to have a result set that looks like:
> >
> > Joe X Y Z
> > Bob A B
> >
> > The first column may have anywhere from 1 to max of 5 entries.
> > Is this possible in SQL, or do I need to do it in PL/SQL?
> >
> > TIA
> >
> > Pete
>
> Your table lacks a column to identify that X goes in column 1, Y in
> column 2 etc., so I have faked one on the assumption that you want
> them ordered by b value:
>
> 1 select a,
> 2 max( decode( rn, 1, b ) ) val1,
> 3 max( decode( rn, 2, b ) ) val2,
> 4 max( decode( rn, 3, b ) ) val3,
> 5 max( decode( rn, 4, b ) ) val4,
> 6 max( decode( rn, 5, b ) ) val5
> 7 from
> 8 ( select a, b, row_number() over (partition by a order by b) as
> rn
> 9 from t1
> 10 )
> 11 group by a;
>
> A V V V V V
> --- - - - - -
> Bob A B
> Joe X Y Z

Thanks for your tip. The original requirement was that they wanted the "child" values concatenated, separated by a space, and I hadn't even thought to break the problem up. So, your tip to first put them into separate columns and then concatenate them will make things easy.

Thanks,

Pete

(Actually, I think though that I will have to put this into PL/SQL because they want to put in some business rules on the ordering of the "child" values.) Received on Fri Jan 30 2004 - 12:19:19 CST