假如有如下表，其中各个i值对应的行数是不定的
SQL> select * from t;
I A D
———- ———- ——————-
1 b 2008-03-27 10:55:42
1 a 2008-03-27 10:55:46
1 d 2008-03-27 10:55:30
2 z 2008-03-27 10:55:55
2 t 2008-03-27 10:55:59
要获得如下结果，注意字符串需要按照D列的时间排序：
1 d,b,a
2 z,t
这是一个比较典型的行列转换，有好几种实现方法
1.自定义函数实现
create or replace function my_concat(n number)
return varchar2
is
type typ_cursor is ref cursor;
v_cursor typ_cursor;
v_temp varchar2(10);
v_result varchar2(4000):= ”;
v_sql varchar2(200);
begin
v_sql := ‘select a from t where i=’ || n ||’ order by d’;
open v_cursor for v_sql;
loop
fetch v_cursor into v_temp;
exit when v_cursor%notfound;
v_result := v_result ||’,’ || v_temp;
end loop;
return substr(v_result,2);
end;
SQL> select i,my_concat(i) from t group by i;
I MY_CONCAT(I)
———- ——————–
1 d,b,a
2 z,t
虽然这种方式可以实现需求，但是如果表t的数据量很大，i的值又很多的情况下，因为针对每个i值都要执行一句select，扫描和排序的次数和i的值成正比，性能会非常差。
2.使用sys_connect_by_path
select i,ltrim(max(sys_connect_by_path(a,’,')),’,') a
from
(
select i,a,d,min(d) over(partition by i) d_min,
(row_number() over(order by i,d))+(dense_rank() over (order by i)) numid
from t
)
start with d=d_min connect by numid-1=prior numid
group by i;
从执行计划上来看，这种方式只需要扫描两次表，比自定义函数的方法，效率要高很多，尤其是表中数据量较大的时候：
3.使用wm_sys.wm_concat
这个函数也可以实现类似的行列转换需求，但是似乎没有办法做到直接根据另外一列排序，所以需要先通过子查询或者临时表排好序
SQL> select i,wmsys.wm_concat(a) from t group by i;
I WMSYS.WM_CONCAT(A)
———- ——————–
1 b,a,d
2 z,t
SQL> select i,wmsys.wm_concat(a)
2 from
3 (select * from t order by i,d)
4 group by i;
I WMSYS.WM_CONCAT(A)
———- ——————–
1 d,b,a
2 z,t
执行计划上看，只需要做一次表扫描就可以了，但是这个函数是加密过的，执行计划并不能显示函数内部的操作。
-----行列转换一
数据格式一
CARD_CODE Q BAL
--------- ---------- ----------
001 1 27
001 2 10
001 3 36
001 4 97
002 1 96
002 2 12
002 3 15
002 4 32
数据格式二
CARD_CODE Q1 Q2 Q3 Q4
--------- ---------- ---------- ---------- ----------
001 27 10 36 97
002 96 12 15 32
--格式一到格式二
SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0)) q1, SUM(decode(a.q, 2, a.bal, 0)) q2,
SUM(decode(a.q, 3, a.bal, 0)) q3, SUM(decode(a.q, 4, a.bal, 0)) q4
FROM my_card a
GROUP BY a.card_code
ORDER BY 1;
--格式二到格式一
SELECT t.card_code, t.rn q, decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal
FROM (SELECT a.*, b.rn
FROM my_card_two a,
(SELECT rownum rn
FROM dual
CONNECT BY rownum <= 4) b) t
ORDER BY 1, 2;
------ 行列转换二
数据格式一
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) q
FROM (SELECT a.card_code, a.q,
row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn
FROM my_card_t3 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;
--格式二到格式一
SELECT t.card_code,
substr(t.q,
instr(';' || t.q, ';', 1, rn),
instr(t.q || ';', ';', 1, rn) - instr(';' || t.q, ';', 1, rn)) q
FROM (SELECT a.card_code, a.q, b.rn
FROM my_card_t4 a,
(SELECT rownum rn
FROM dual
CONNECT BY rownum <= 100) b
WHERE instr(';' || a.q, ';', 1, rn) > 0) t
ORDER BY 1, 2;