Oracle行列转换函数 -- wm_concat、listagg

Oracle行列转换函数 – wm_concat、listagg

1、环境搭建（Oracle 11g）

CREATE TABLE temp_dep(
	id INT,
	depId INT,
	name varchar(20),
	age INT,
	address varchar(100),
	brithday date
);


INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (1, 102, '张三', 25, '云南昆明', null);
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (2, 101, '李四', 22, '四川成都', null);
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (3, 103, '王五', 25, '云南昆明',  TO_DATE('2000-05-19 17:15:33','yyyy-MM-dd hh24:mi:ss'));
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (4, 102, '赵六', 23, '湖北武汉', null);
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (5, 101, '钱七', 27, '云南昆明', null);
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (6, 103, '小六', 28, '四川成都', null);
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (7, 102, '小七', 26, '湖北武汉',  TO_DATE('2011-05-21 17:15:33','yyyy-MM-dd hh24:mi:ss'));
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (8, 101, '小八', 22, '广东深圳', null);
INSERT INTO temp_dep(id, depId, name, age, address, brithday) VALUES (9, 103, '小九', 25, '广东深圳', null);

SQL>SELECT * FROM temp_dep;
ID	DEPID	NAME	AGE	 ADDRESS	BRITHDAY
1	102	    张三		25	云南昆明	[NULL]
2	101		李四		22	四川成都	[NULL]
3	103		王五		25	云南昆明	2000-05-19 17:15:33
4	102		赵六		23	湖北武汉	[NULL]
5	101		钱七		27	云南昆明	[NULL]
6	103		小六		28	四川成都	[NULL]
7	102		小七		26	湖北武汉	2011-05-21 17:15:33
8	101		小八		22	广东深圳	[NULL]
9	103		小九		25	广东深圳	[NULL]

2、wm_concat

wm_concat函数可以分组拼接，但无法排序后再拼接，且分割符只能是逗号，局限较多。

SQL> SELECT WM_CONCAT(NAME) FROM temp_dep;
WM_CONCAT(NAME)
张三,李四,王五,赵六,钱七,小六,小七,小八,小九

SQL> SELECT WM_CONCAT(NAME) FROM temp_dep ORDER BY age;
WM_CONCAT(NAME)
张三,李四,王五,赵六,钱七,小六,小七,小八,小九

SQL> SELECT depid,WM_CONCAT(NAME) FROM temp_dep GROUP BY depId ORDER BY depid;
DEPID	WM_CONCAT(NAME)
101	    李四,小八,钱七
102	    张三,小七,赵六
103	    王五,小九,小六

3、listagg

-- listagg语法
-- LISTAGG (measure_column [, 'delimiter'])
-- WITHIN GROUP (ORDER BY order_by_clause) [OVER (PARTITION BY query_partition_clause)];

-- 无分隔符
SQL> SELECT LISTAGG(name) WITHIN GROUP (ORDER BY age) FROM temp_dep; 
LISTAGG(NAME)WITHINGROUP(ORDERBYAGE)
李四小八赵六王五小九张三小七钱七小六

-- 指定分隔符
SQL> SELECT LISTAGG(name, ';') WITHIN GROUP (ORDER BY age) FROM temp_dep; 
LISTAGG(NAME,';')WITHINGROUP(ORDERBYAGE)
李四;小八;赵六;王五;小九;张三;小七;钱七;小六

-- 分组拼接
SQL> SELECT age,LISTAGG(name, ';') WITHIN GROUP (ORDER BY age) OVER(PARTITION BY AGE) FROM temp_dep;
AGE	LISTAGG(NAME,';')WITHINGROUP(ORDERBYAGE)OVER(PARTITIONBYAGE)
22	李四;小八
22	李四;小八
23	赵六
25	张三;王五;小九
25	张三;王五;小九
25	张三;王五;小九
26	小七
27	钱七
28	小六

-- 分组拼接且去重，以下两种写法皆可，结果是一样的
SQL> SELECT DISTINCT AGE, LISTAGG(name, ';') WITHIN GROUP (ORDER BY AGE) OVER(PARTITION BY AGE) FROM temp_dep ORDER BY AGE; 
SQL> SELECT DISTINCT AGE, LISTAGG(name, ';') WITHIN GROUP (ORDER BY AGE) FROM temp_dep GROUP BY AGE ORDER BY AGE; 
AGE	LISTAGG(NAME,';')WITHINGROUP(ORDERBYAGE)OVER(PARTITIONBYAGE)
22	李四;小八
23	赵六
25	张三;王五;小九
26	小七
27	钱七
28	小六