Re: How to flatten cols into rows

From: Tony <andrewst_at_onetel.net.uk>
Date: 30 Jan 2004 04:24:07 -0800
Message-ID: <c0e3f26e.0401300424.4548e051@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 Received on Fri Jan 30 2004 - 06:24:07 CST