Re: Column aggregating for text columns

From: Tokunaga T. <tonkuma_at_jp.ibm.com>
Date: 24 May 2002 02:02:34 -0700
Message-ID: <8156d9ae.0205240102.11c7b34c_at_posting.google.com>

svenigalla_at_netsetgo.com (Srini Venigalla) wrote in message news:<22597f96.0205231116.4a741078_at_posting.google.com>...
> Continuing my previous post:
>
> Will the query still work, if you had more than 3 rows per ID. Some thing like:
>
> 123 A
> 123 B
> 123 C
> 123 D
> 222 A
> 222 B
> 333 K
> 333 L
> 333 M
> 333 N
>
> .. so on

Need a little change.
But it depends on how to arrange/map extra values to three columns.  

For example, if you want to arrange horizontally. SELECT id

      ,max(CASE WHEN mod(rownum,3) = 1 THEN value ELSE NULL END) AS Value1
      ,max(CASE WHEN mod(rownum,3) = 2 THEN value ELSE NULL END) AS Value2
      ,max(CASE WHEN mod(rownum,3) = 0 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, (rownum-1)/3
 ORDER BY id, Value1
;

---

 

ID VALUE1 VALUE2 VALUE3
------ ------ ------ ------

   123 A      B      C      
   123 D      -      -      
   222 A      B      -      
   333 K      L      M      
   333 N      O      P      
   333 Q      -      -      
 
 

Or vertically.
SELECT nbr.id

      ,max(CASE WHEN rownum <= rowcnt      THEN value ELSE NULL END) AS Value1
      ,max(CASE WHEN (rownum-1)/rowcnt = 1 THEN value ELSE NULL END) AS Value2
      ,max(CASE WHEN rownum  > rowcnt*2    THEN value ELSE NULL END) AS Value3
  FROM (SELECT id
              ,value
              ,rownumber() over(partition by id order by value)
          FROM Agr_test
       ) AS nbr (id, value, rownum)
      ,(SELECT id, (count(*)+2)/3
          FROM Agr_test
         GROUP BY id
       ) AS cnt (id, rowcnt)

 WHERE nbr.id = cnt.id
 GROUP BY nbr.id, mod(rownum,rowcnt)
 ORDER BY id, Value1
;

---

 

ID VALUE1 VALUE2 VALUE3

------ ------ ------ ------ 
   123 A      C      -      
   123 B      D      -      
   222 A      B      -      
   333 K      N      Q      
   333 L      O      -      
   333 M      P      -

Received on Fri May 24 2002 - 11:02:34 CEST