通過 SQL 實現行轉列（列的資料條目、資料分佈是不規則的）

--建立測試表
create table test(report_date date,activity_name varchar2(15),amount number);


insert into test values(to_date('2015-10-01','yyyy-mm-dd'),'活動一',1300);
insert into test values(to_date('2015-10-02','yyyy-mm-dd'),'活動一',500);
insert into test values(to_date('2015-10-02','yyyy-mm-dd'),'活動二',800);
insert into test values(to_date('2015-10-03','yyyy-mm-dd'),'活動一',900);
insert into test values(to_date('2015-10-03','yyyy-mm-dd'),'活動二',1500);
insert into test values(to_date('2015-10-03','yyyy-mm-dd'),'活動三',3500);
commit;


SQL> select * from test;


REPORT_DAT ACTIVITY_NAME  AMOUNT
----------         ---------------           ----------
2015-10-01 活動一                1300
2015-10-02 活動一                 500
2015-10-02 活動二                 800
2015-10-03 活動一                 900
2015-10-03 活動二                1500
2015-10-03 活動三                3500


已選擇6行。


想要實現的效果是：


---------- --------------- ----------
2015-10-01 活動一                1300
2015-10-02 活動一                 500 活動二                 800
2015-10-03 活動一                 900 活動二                1500 活動三                3500


可以發現，列的資料條目是不規則的


對於這種需求，想要通過SQL實現的話，不能使用 decode 函式（列的條目相同的需求，可以使用 decode 函式來實現）


方法一，在 Oracle 11g，可以使用 LISTAGG 這個分析函式來實現

SELECT report_date,
       LISTAGG(activity_name || ',  ' || amount || ', ') within GROUP(ORDER BY report_date) info
  FROM test
 GROUP BY report_date;


REPORT_DAT INFO
---------- ---------------------------------------------
2015-10-01 活動一,  1300,
2015-10-02 活動二,  800, 活動一,  500,
2015-10-03 活動二,  1500, 活動三,  3500, 活動一,  900,


方法二，通過層次函式 CONEECT BY 實現

with tmp as
 (select report_date, activity_name || ', ' || amount info, rn
    from (select report_date,
                 activity_name,
                 ROW_NUMBER() OVER(PARTITION BY report_date ORDER BY activity_name) rn,
                 amount
            from test))
select a.report_date, b.message
  from(
select report_date, max(level_no) no
  from(
select report_date, LTRIM(SYS_CONNECT_BY_PATH(info, '/ '), '/') message, level level_no
  from tmp
connect by prior report_date = report_date and prior rn = rn + 1)
 group by report_date) a,(
select report_date, LTRIM(SYS_CONNECT_BY_PATH(info, '/ '), '/') message, level level_no
  from tmp
connect by prior report_date = report_date and prior rn = rn - 1) b
 where a.report_date = b.report_date and a.no = b.level_no;

方法三，在 Oracle 10g ，使用 WM_CONCAT 函式來實現

SELECT report_date, MAX(info)
from (SELECT report_date,
       WM_CONCAT(activity_name || ',  ' || amount || ', ') OVER (PARTITION BY report_date ORDER BY report_date) info
  FROM test)
GROUP BY report_date;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-1869259/，如需轉載，請註明出處，否則將追究法律責任。