Oracle实现多行拼接/行转列拼接


数据格式一

—————————————————————————————————————————

CARD_CODE   Q
———   ————————————————
001               quarter_1
001               quarter_2
001               quarter_3
001               quarter_4
002               quarter_1
002               quarter_2
002               quarter_3
002               quarter_4

数据格式二
CARD_CODE   Q
———   ——————————————————————————–
002               quarter_1;quarter_2;quarter_3;quarter_4
001               quarter_1;quarter_2;quarter_3;quarter_4

从格式一到格式二

SELECT   t1.card_code,   substr(MAX(sys_connect_by_path(t1.q,   ‘; ‘)),   2)
FROM   (SELECT   a.card_code,
a.q,
row_number()   over(PARTITION   BY   a.card_code   ORDER   BY   a.q)   rn
FROM   t_change_lc_comma   a)   t1
START   WITH   t1.rn   =   1
CONNECT   BY   t1.card_code   =   PRIOR   t1.card_code
AND   t1.rn   –   1   =   PRIOR   t1.rn
GROUP   BY   t1.card_code

 

Advertisements

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s