Re: Column aggregating for text columns

From: Tokunaga T. <tonkuma_at_jp.ibm.com>
Date: 23 May 2002 02:03:04 -0700
Message-ID: <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 Received on Thu May 23 2002 - 11:03:04 CEST