oracle多字段拼接,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

39.904667

116.408198