Oracle多行資料合併

1. 多個未知數值合并

SELECToperate_id, max(substr(phone_id, 2)) phone_id

 FROM (SELECT operate_id, sys_connect_by_path(phone_id, ',') phone_id

         FROM (SELECT operate_id,

                       phone_id,

                       operate_id || rn rchild,

                       operate_id || (rn - 1)rfather

                 FROM (SELECTtest.operate_id,

                               test.phone_id,

                               row_number()over(PARTITION BY test.operate_id ORDER BY test.phone_id) rn

                          FROM lq_card_infotest))

       CONNECT BY PRIOR rchild = rfather

        START WITH rfather LIKE '%0')

 GROUP BY operate_id

 

解析：其中operate_id是要被分組的欄位，phone_id是要被展示的欄位

 

2. 一定小範圍已知資料合併

select t.operate_id,

sum(case when t.phone_id='18937278888' then t.phone_id end) no1,

sum(case when t.phone_id='13303723322' then t.phone_id end) no2,

sum(case when t.phone_id='18937245684' then t.phone_id end) no3,

sum(case when t.phone_id='13323729424' then t.phone_id end) no4

from lq_card_info t  group by t.operate_id order BY t.operate_id

 

解析：其中operate_id是要被分組的欄位，phone_id是要被展示的欄位