Re: Column aggregating for text columns

From: Srini Venigalla <svenigalla_at_netsetgo.com>
Date: 23 May 2002 12:07:33 -0700
Message-ID: <22597f96.0205231107.709d0f5d_at_posting.google.com>


tonkuma_at_jp.ibm.com (Tokunaga T.) wrote in message news:<8156d9ae.0205230103.1576be22_at_posting.google.com>...
> tonkuma_at_jp.ibm.com (Tokunaga T.) wrote in message news:<8156d9ae.0205220244.6f3d9269_at_posting.google.com>...
> > "Sirin Venigalla" <a_at_abc.com> wrote in message news:<WoCG8.90740$2G1.31110535_at_typhoon.nyroc.rr.com>...
> > > I found this interesting question on another forum. I am going to work on
> > > it, but if anyone has a solution please post it.
> > >
> > > --------------------------------------
> > > How do i convert this data in a table
> > > id value
> > > 123 abc
> > > 123 cde
> > > 123 xxx
> > > to a query output
> > >
> > > id value1 value2 value3
> > > 123 abc cde xxx
> > >
> > > I am using DB2 UDB 7.X
> >
> > My question is how to map value 'abc' to column value1, value 'cde' to
> > column value2 and value 'xxx' to column value3.
> > If you could clarify that point, it would be not so difficult to solve
> > this problem.
>
> If your requirement is mapping values by collating sequence in each id(group),
> try this.
> (I tested this on my DB2 UDB for Windows V7.2)
>
> -------------------------------------
> SELECT * FROM Agr_test;
> ---------------------------------------------------
>
> ID VALUE
> ------ -----
> 123 abc
> 123 cde
> 123 xxx
> 456 abc
> 456 fgh
> 789 cde
> 789 ijk
> 789 lmn
>
> -------------------------------------
> SELECT id
> ,max(CASE WHEN rownum = 1 THEN value ELSE NULL END) AS Value1
> ,max(CASE WHEN rownum = 2 THEN value ELSE NULL END) AS Value2
> ,max(CASE WHEN rownum = 3 THEN value ELSE NULL END) AS Value3
> FROM (SELECT id
> ,value
> ,rownumber() over(partition by id order by value)
> FROM Agr_test
> ) AS Q (id, value, rownum)
> GROUP BY id
> ;
> ---------------------------------------------------
>
> ID VALUE1 VALUE2 VALUE3
> ------ ------ ------ ------
> 123 abc cde xxx
> 456 abc fgh -
> 789 cde ijk lmn

That is nice. I like it a lot. It solves the problem without resorting to recursion, as I did in my solution.

Thanks. Received on Thu May 23 2002 - 21:07:33 CEST

Original text of this message