DB2 行变列

转自 http://www.itpub.net/viewthread.php?tid=830747&page=2#pid8282888

select * from t1

CARD_CODE QTR
--------- --------------------
        1 quarter_1
        1 quarter_2
        1 quarter_3
        1 quarter_4
        2 quarter_1
        2 quarter_2
        2 quarter_3
        2 quarter_4
with tt as ( select row_number() over (partition by card_code order by qtr) as parent, row_number() over (partition by card_code order by qtr)+1 as child, card_code, qtr from t1 ), t2 (parent, child, card_code, qtr) as ( select parent, child, card_code, cast(qtr as char(200)) from tt where parent=1 union all select t2.child, t2.child+1, t2.card_code, cast(rtrim(t2.qtr) || ',' || rtrim(tt.qtr) as char(200)) from t2, tt where t2.child=tt.parent and t2.card_code=tt.card_code ) select card_code, qtr from t2 where t2.child=( select max(child) from tt where t2.card_code=tt.card_code )

 

1 quarter_1,quarter_2,quarter_3,quarter_4                                                                             
        2 quarter_1,quarter_2,quarter_3,quarter_4                                                                             

  2 record(s) selected with 1 warning messages printed.

 

 

这里还有一篇更强大的:http://www.ibm.com/developerworks/cn/data/library/techarticles/0110lyle/0110lyle.html