行转列方法，decode()与wmsys.wm_concat()

SQL>select * from test_tb_grade t;

	ID	USER_NAME	COURSE	SCORE
1	1	a	ch	90
2	2	a	math	98
3	3	a	en	95
4	4	b	en	99
5	5	b	math	92
6	6	b	ch	94
7	7	c	ch	91
8	8	c	math	90
9	9	c	en

需求：按学生名分组，查询各科成绩。

一、使用decode()函数：

第一步：将各科转换成列，并显示对应分数，如果没有的则为空，如下表：

SQL>SELECT T.USER_NAME,

       DECODE(T.COURSE, 'ch', NVL(SCORE, 0)) AS CHINESE,

       DECODE(T.COURSE, 'math', SCORE, NULL) AS MATH,

       DECODE(T.COURSE, 'en', NVL(SCORE, 0)) AS ENGLISH

  FROM TEST_TB_GRADE T;

使用NVL()函数是考虑到表中有值为NULL。

	USER_NAME	CHINESE	MATH	ENGLISH
1	a	90
2	a		98
3	a			95
4	b			99
5	b		92
6	b	94
7	c	91
8	c		90
9	c			0

第二步：合并结果并分组

SQL>SELECT T.USER_NAME,

       MAX(DECODE(T.COURSE, 'ch', SCORE, NULL)) AS CHINESE,

       MAX(DECODE(T.COURSE, 'math', SCORE, NULL)) AS MATH,

       MAX(DECODE(T.COURSE, 'en', NVL(SCORE, 0))) AS ENGLISH

  FROM TEST_TB_GRADE T

 GROUP BY T.USER_NAME

 ORDER BY T.USER_NAME ;

	USER_NAME	CHINESE	MATH	ENGLISH
1	a	90	98	95
2	b	94	92	99
3	c	91	90	0

二、使用wmsys.wm_concat()函数

1、直接使用wmsys.wm_concat()函数合并( 满足行转列分组要求，但结果为乱序)

SQL>SELECT USER_NAME,

       WMSYS.WM_CONCAT(T.COURSE) COURSE,

       WMSYS.WM_CONCAT(T.SCORE) SCORE

  FROM TEST_TB_GRADE T

 GROUP BY T.USER_NAME ;

	USER_NAME	COURSE	SCORE
1	a	ch,math,en	90,95,98
2	b	en,math,ch	99,94,92
3	c	ch,math,en	91,90

将结果与之前使用decode()函数的相比，可以发现现在的结果中合并列COURSE、SCORE中的值呈乱序排列。

2、合并时排序( 满足行转列分组要求，结果已排序)

第一步：使用分析函数按USER_NAME 分组后以COURSE排序。如下表：

SQL>SELECT T.USER_NAME,

       WMSYS.WM_CONCAT(COURSE) OVER(PARTITION BY USER_NAME ORDER BY COURSE) COURSE,

       WMSYS.WM_CONCAT(NVL(SCORE, 0)) OVER(PARTITION BY USER_NAME ORDER BY COURSE) SCORE

  FROM TEST_TB_GRADE T;

	USER_NAME	COURSE	SCORE
1	a	ch	90
2	a	ch,en	90,95
3	a	ch,en,math	90,95,98
4	b	ch	94
5	b	ch,en	94,99
6	b	ch,en,math	94,99,92
7	c	ch	91
8	c	ch,en	91,0
9	c	ch,en,math	91,0,90

第二步：建一伪列TOP来给每个分组中排序列号，取出其中TOP为1的数据即为需求结果，如下表：

SQL>SELECT *

  FROM (SELECT T.USER_NAME,

               WMSYS.WM_CONCAT(COURSE) OVER(PARTITION BY USER_NAME ORDER BY COURSE) COURSE,

               WMSYS.WM_CONCAT(NVL(SCORE, 0)) OVER(PARTITION BY USER_NAME ORDER BY COURSE) SCORE,

                 ROW_NUMBER() OVER(PARTITION BY USER_NAME ORDER BY COURSE DESC) TOP

          FROM TEST_TB_GRADE T)

 WHERE TOP = 1 ;

	USER_NAME	COURSE	SCORE	TOP
1	a	ch,en,math	90,95,98	1
2	b	ch,en,math	94,99,92	1
3	c	ch,en,math	91,0,90	1

总结：由上面的例子中可以知道，decode()方法的行转列需要确定列中的值，在已知列值的情况下使用decode()可以获得更好的展现效果。 而wmsys.wm_concat()更为灵活。

-----------------------------------------------------------------------------------------------------------------------------------

对wmsys.wm_concat()函数的补充

1、行转列后变成一列，显示格式为“学科(分数)”，并且按学科名排序

SQL>SELECT user_name,r

  FROM (SELECT USER_NAME,

               WMSYS.WM_CONCAT(T.COURSE || '(' || T.SCORE || ')') OVER(PARTITION BY T.USER_NAME ORDER BY T.COURSE) r,

               ROW_NUMBER() OVER(PARTITION BY T.USER_NAME ORDER BY T.COURSE DESC) B

          FROM TEST_TB_GRADE T)

 WHERE B = 1 ;

	USER_NAME	R
1	a	ch(90),en(95),math(98)
2	b	ch(94),en(99),math(92)
3	c	ch(91),en(),math(90)

这段SQL其实是上段SQL的简单修改，将两个字段合并后放在wmsys.wm_concat()函数，然后同样是使用分析函数排序，同样是建一伪列来过滤。

2、不使用row_number()伪列来过滤数据

1）行转列，分两列，并排序

SQL>SELECT USER_NAME, MAX(COURSE), MAX(SCORE)

  FROM (SELECT T.USER_NAME,

               WMSYS.WM_CONCAT(T.COURSE) OVER(PARTITION BY T.USER_NAME ORDER BY T.COURSE) COURSE,

               WMSYS.WM_CONCAT(NVL(T.SCORE, 0)) OVER(PARTITION BY T.USER_NAME ORDER BY T.COURSE) SCORE

          FROM TEST_TB_GRADE T)

 GROUP BY USER_NAME;

子查询里与上面的SQL一样，但是去掉了row_number()，而使用group by分组与max()函数。与使用decode()函数中max()一样。

2）行转列，合一列，并排序

SQL>SELECT USER_NAME, MAX(A)

  FROM (SELECT T.USER_NAME,

               WMSYS.WM_CONCAT(COURSE || '(' || SCORE || ')') OVER(PARTITION BY USER_NAME ORDER BY COURSE) A

          FROM TEST_TB_GRADE T)

 GROUP BY USER_NAME;

同理。