通过 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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-1869259/