原文链接：
 http://www.360doc.com/content/18/0525/20/14808334_757019563.shtml
 概述
 好久没写SQL语句，今天看到问答中的一个问题，拿来研究一下。
 
问题链接：关于Mysql 的分级输出问题
 
情景简介
 学校里面记录成绩，每个人的选课不一样,而且以后会添加课程，所以不需要把所有课程当作列。数据表里面数据如下图，使用姓名+课程作为联合主键（有些需求可能不需要联合主键）。本文以MySQL为基础，其他数据库会有些许语法不同。
 
数据库表数据：
 
 
 
处理后的结果（行转列）：
 
 
 方法一：
 
这里可以使用Max，也可以使用Sum；
 
注意第二张图，当有学生的某科成绩缺失的时候，输出结果为Null;
 
SELECT  
    SNAME,  
    MAX(  
        CASE CNAME  
        WHEN 'JAVA' THEN  
            SCORE  
        END  
    ) JAVA,  
    MAX(  
        CASE CNAME  
        WHEN 'mysql' THEN  
            SCORE  
        END  
    ) mysql  
FROM  
    stdscore  
GROUP BY  
    SNAME;  
 
可以在第一个Case中加入Else语句解决这个问题:
 
SELECT  
    SNAME,  
    MAX(  
        CASE CNAME  
        WHEN 'JAVA' THEN  
            SCORE  
        ELSE  
            0  
        END  
    ) JAVA,  
    MAX(  
        CASE CNAME  
        WHEN 'mysql' THEN  
            SCORE  
        ELSE  
            0  
        END  
    ) mysql  
FROM  
    stdscore  
GROUP BY  
    SNAME;  
 
方法二：
 
SELECT DISTINCT  a.sname,  
(SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='JAVA' ) AS 'JAVA',  
(SELECT score FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='mysql' ) AS 'mysql'  
FROM stdscore a  
 
方法三：
 
DROP PROCEDURE  
IF EXISTS sp_score;  
DELIMITER &&  
  
CREATE PROCEDURE sp_score ()  
BEGIN  
    #课程名称  
    DECLARE  
        cname_n VARCHAR (20) ; #所有课程数量  
        DECLARE  
            count INT ; #计数器  
            DECLARE  
                i INT DEFAULT 0 ; #拼接SQL字符串  
            SET @s = 'SELECT sname' ;  
            SET count = (  
                SELECT  
                    COUNT(DISTINCT cname)  
                FROM  
                    stdscore  
            ) ;  
            WHILE i < count DO  
  
  
            SET cname_n = (  
                SELECT  
                    cname  
                FROM  
                    stdscore  
                GROUP BY CNAME   
                LIMIT i,  
                1  
            ) ;  
            SET @s = CONCAT(  
                @s,  
                ', SUM(CASE cname WHEN ',  
                '\'',  
                cname_n,  
                '\'',  
                ' THEN score ELSE 0 END)',  
                ' AS ',  
                '\'',  
                cname_n,  
                '\''  
            ) ;  
            SET i = i + 1 ;  
            END  
            WHILE ;  
            SET @s = CONCAT(  
                @s,  
                ' FROM stdscore GROUP BY sname'  
            ) ; #用于调试  
            #SELECT @s;  
            PREPARE stmt  
            FROM  
                @s ; EXECUTE stmt ;  
            END&&  
  
CALL sp_score () ;  
 
处理后的结果（行转列）分级输出：
 
 
 方法一：
 这里可以使用Max，也可以使用Sum；
 
注意第二张图，当有学生的某科成绩缺失的时候，输出结果为Null;
 
SELECT  
    SNAME,  
    MAX(  
        CASE CNAME  
        WHEN 'JAVA' THEN  
            (  
                CASE  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN  
                    '优秀'  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN  
                    '良好'  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN  
                    '普通'  
                ELSE  
                    '较差'  
                END  
            )  
        END  
    ) JAVA,  
    MAX(  
        CASE CNAME  
        WHEN 'mysql' THEN  
            (  
                CASE  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN  
                    '优秀'  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN  
                    '良好'  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN  
                    '普通'  
                ELSE  
                    '较差'  
                END  
            )  
        END  
    ) mysql  
FROM  
    stdscore  
GROUP BY  
    SNAME;  
 
方法二：
 
SELECT DISTINCT  a.sname,  
(SELECT (  
                CASE  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN  
                    '优秀'  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN  
                    '良好'  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN  
                    '普通'  
                ELSE  
                    '较差'  
                END  
            ) FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='JAVA' ) AS 'JAVA',  
(SELECT (  
                CASE  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 20 THEN  
                    '优秀'  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') > 10 THEN  
                    '良好'  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME='JAVA') >= 0 THEN  
                    '普通'  
                ELSE  
                    '较差'  
                END  
            ) FROM stdscore b WHERE a.sname=b.sname AND b.CNAME='mysql' ) AS 'mysql'  
FROM stdscore a  
 
方法三：
 
DROP PROCEDURE  
IF EXISTS sp_score;  
DELIMITER &&  
  
CREATE PROCEDURE sp_score ()  
BEGIN  
    #课程名称  
    DECLARE  
        cname_n VARCHAR (20) ; #所有课程数量  
        DECLARE  
            count INT ; #计数器  
            DECLARE  
                i INT DEFAULT 0 ; #拼接SQL字符串  
            SET @s = 'SELECT sname' ;  
            SET count = (  
                SELECT  
                    COUNT(DISTINCT cname)  
                FROM  
                    stdscore  
            ) ;  
            WHILE i < count DO  
  
  
            SET cname_n = (  
                SELECT  
                    cname  
                FROM  
                    stdscore  
        GROUP BY CNAME   
                LIMIT i, 1  
            ) ;  
            SET @s = CONCAT(  
                @s,  
                ', MAX(CASE cname WHEN ',  
                '\'',  
                cname_n,  
                '\'',  
                ' THEN (  
                CASE  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') > 20 THEN  
                    \'优秀\'  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') > 10 THEN  
                    \'良好\'  
                WHEN SCORE - (select avg(SCORE) from stdscore where CNAME=\'',cname_n,'\') >= 0 THEN  
                    \'普通\'  
                ELSE  
                    \'较差\'  
                END  
            ) END)',  
                ' AS ',  
                '\'',  
                cname_n,  
                '\''  
            ) ;  
            SET i = i + 1 ;  
            END  
            WHILE ;  
            SET @s = CONCAT(  
                @s,  
                ' FROM stdscore GROUP BY sname'  
            ) ;   
            #用于调试  
            #SELECT @s;  
            PREPARE stmt  
            FROM  
                @s ; EXECUTE stmt ;  
            END&&  
  
  
CALL sp_score ();  
 
几种方法比较分析
 第一种使用了分组，对每个课程分别处理。
 第二种方法使用了表连接。
 第三种使用了存储过程，实际上可以是第一种或第二种方法的动态化，先计算出所有课程的数量，然后对每个分组进行课程查询。这种方法的一个最大的好处是当新增了一门课程时，SQL语句不需要重写。
 
小结
 关于行转列和列转行
 
这个概念似乎容易弄混，有人把行转列理解为列转行，有人把列转行理解为行转列；
 
这里做个定义：
 
行转列：把表中特定列（如本文中的：CNAME）的数据去重后做为列名（如查询结果行中的“Java，mysql”，处理后是做为列名输出）；
 
列转行：可以说是行转列的反转，把表中特定列（如本文处理结果中的列名“JAVA，mysql”）做为每一行数据对应列“CNAME”的值；
 
关于效率
 
不知道有什么好的生成模拟数据的方法或工具，麻烦小伙伴推荐一下，抽空我做一下对比；
 
还有其它更好的方法吗？
 
本文使用的几种方法应该都有优化的空间，特别是使用存储过程的话会更加灵活，功能更强大；
 
本文的分级只是给出一种思路，分级的方法如果学生的成绩相差较小的话将失去意义；
 
如果小伙伴有更好的方法，还请不吝赐教，感激不尽！
 
有些需求可能不需要联合主键
 
有些需求可能不需要联合主键，因为一门课程可能允许学生考多次，取最好的一次成绩，或者取多次的平均成绩。
 最简单的case when
 
SELECT
	COUNT(*) AS num,
	(
		CASE PAY_TYPE
		WHEN '0' THEN
			'微信支付'
		WHEN '1' THEN
			'支付宝支付'
		WHEN '2' THEN
			'无感支付'
		WHEN '3' THEN
			'银联'
		WHEN '4' THEN
			'白名单'
		WHEN '5' THEN
			'月卡'
		END
	) AS type
FROM
	t_park_order
GROUP BY
	PAY_TYPE;

 
关于oracle行转列函数
 
LISTAGG()
XMLAGG()

 
LISTAGG()
 
 
例如:
 

 查出每个职位的所有人名单：
 
 
 
但是如果遇到转为一行后的字段过于太长太长，会报错，如下：
 
 

 这样只有使用另一种方法查出来转为行的结果类型转为clob
 LISTAGG函数返回的是一个varchar2类型的数据，最大字节长度为4000
 XMLAGG函数返回的类型为CLOB，最大字节长度为32767
 
 
XMLAGG()
 
 
例如:
 

 查出每个职位的所有人名单：
 
 点开clob每个字段内容看看：
 

 
 

转自：https://blog.csdn.net/qq_33157666/article/details/72854801
 
一、简单介绍
 
最近项目需要进行行转列，经过上网查资料发现了wmsys.wm_concat和LISTAGG函数，在这分享给大家
 
wmsys.wm_concat是oracle 10g推出的，用来连接字符串，LISTAGG是oracle 11g推出的，它的作用和wmsys.wm_concat是一样的，但是他不支持LISTAGG。
 
二、具体用法
 
现在以oracle的emp表为例，现在emp标的结构如下图:
 
 
 
 
 
如果我们想要查询出每个部门都有哪些员工，如下表格的结构，就需要用wmsys.wm_concat或LISTAGG函数了。
 
 
deptno
 
 
ename
 
 
10
 
 
CLARK,MILLER,KING
 
 
20
 
 
SMITH,FORD,ADAMS,SCOTT,JONES
 
 
30
 
 
ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
 
(表格1)
 
1、wmsys.wm_concat的用法：
 
select deptno,wmsys.wm_concat(ename) ename from emp group by deptno;
 
wmsys.wm_concat()中的参数也可以使多个，使用”||”拼接，如下例子：
 
select deptno,wmsys.wm_concat(ename || '-' || job) name from emp group by deptno;
 
运行的结果如下表格：
 
 
deptno
 
 
ename
 
 
10
 
 
CLARK-MANAGER,MILLER-CLERK,KING-PRESIDENT
 
 
 
 
20
 
 
SMITH-CLERK,FORD-ANALYST,ADAMS-CLERK,SCOTT-ANALYST,JONES-MANAGER
 
 
30
 
 
ALLEN-SALESMAN,JAMES-CLERK,TURNER-SALESMAN,BLAKE-MANAGER,MARTIN-SALESMAN,WARD-SALESMAN
 
(表格2)
 
  
 
2、LISTAGG的用法：
 
select deptno,LISTAGG(ename,',') withinGRO(order by ename) from emp group by deptno;
 
此条sql的执行结果如表格1.
 
 

PIVOT 提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单和更具可读性。
 
以下是带批注的 PIVOT 语法：
 
SELECT <非透视的列>,
    [第一个透视的列] AS <列名称>,
    [第二个透视的列] AS <列名称>,
    ...
    [最后一个透视的列] AS <列名称>,
FROM
    (<生成数据的 SELECT 查询>)
    AS <源查询的别名>
PIVOT
(
    <聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
    IN ( [第一个透视的列], [第二个透视的列],
    ... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;
 
下面举个实例，比如要统计亚马逊9月1号一天每个小时下发的订单量，按照传统的写法如下所示：
 
select Warehouseid 仓库,
	   CustomerID 客户,
	   SUBSTRING(CONVERT(varchar(100),OrderTime,120),0,11) WMS创建时间,
       SUBSTRING(CONVERT(varchar(100), ordertime, 120),12,2) 小时,
       count(OrderNo) 订单量
from DOC_Order_Header
where 
OrderTime>='2018-09-01 00:00:00'
and  OrderTime<='2018-09-01 23:59:59'
and CustomerID ='YMX'
and Warehouseid in('HKBT','FLC')
group by Warehouseid,CustomerID,SUBSTRING(CONVERT(varchar(100),OrderTime,120),0,11),
		SUBSTRING(CONVERT(varchar(100), ordertime, 120),12,2)
order by 仓库,WMS创建时间

 
查询出来的结果为列模式，看起来不方便，如下所示：
 
 
现在使用 PIVOT 函数，把列转成行，看起来直观明了，语法如下所示：
 
select *,
	  [00]+[01]+[02]+[03]+[04]+[05]+[06]+[07]+[08]+[09]+[10]+[11]+
      [12]+[13]+[14]+[15]+[16]+[17]+[18]+[19]+[20]+[21]+[22]+[23]+[24] as 当天订单总数
from
(
	select Warehouseid 仓库,
		   CustomerID 客户,
		   SUBSTRING(CONVERT(varchar(100),OrderTime,120),0,11) WMS创建时间,
	       SUBSTRING(CONVERT(varchar(100), ordertime, 120),12,2) 小时,OrderNo
	       from DOC_Order_Header
     where 
     OrderTime>='2018-09-01 00:00:00'
     and  OrderTime<='2018-09-01 23:59:59'
     and CustomerID ='YMX'
     and Warehouseid in('HKBT','FLC')
)t
PIVOT 
(
    count(t.OrderNo) FOR t.小时 IN
    ([00],[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],
     [12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24])    
)as a
order by a.仓库,a.WMS创建时间
 
查询结果如下图所示：