经典行转列，并配合聚合函数做统计

MySQL没有专门的行转列

一、经典行转列

1.行转列

将此表

转化为以下格式

xingmign

代码

SELECT 姓名,MAX(case 课程 when '语文' THEN 分数 END) as 语文,
						MAX(case 课程 when '数学' THEN 分数 END) as 数学,
						MAX(case 课程 when '物理' THEN 分数 END) as 物理
FROM tb
GROUP BY 姓名

或

    select 姓名 as 姓名 ,
    max(case 课程 when '语文' then 分数 else 0 end) 语文,
    max(case 课程 when '数学' then 分数 else 0 end) 数学,
    max(case 课程 when '物理' then 分数 else 0 end) 物理
    from tb
    group by 姓名

注意：如果不加聚合函数，将是空

2.列转行

创建与已有的表结构数据相同的表，使用select

CREATE TABLE grade     
select 姓名 as 姓名 ,
    max(case 课程 when '语文' then 分数 else 0 end) 语文,
    max(case 课程 when '数学' then 分数 else 0 end) 数学,
    max(case 课程 when '物理' then 分数 else 0 end) 物理
    from tb
    group by 姓名

将此表转为

分析

1.由于只有两列，因此每次所有人的一个课程，即查出一科

2.使用union将相同的属性类别，即其他科合并

代码

SELECT 姓名,'语文' as 课程,语文 as 分数 from grade
UNION
SELECT 姓名,'数学' as 课程,数学 as 分数 from grade
UNION
SELECT 姓名,'物理' as 课程,语文 as 物理 from grade

 

二、经典行转列，并配合聚合函数做统计，现要求统计各个城市，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出结果

有能耗表如下：其中，E_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗

SELECT 
	E_CODE,
	SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--水耗
	SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--电耗
	SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--热耗
FROM 
	THTF_ENERGY_TEST
GROUP BY
	E_CODE

输出结果如下：

三、pivot是sql server 2005 提供的运算符

pivot是sql server 2005 提供的运算符，所以只要数据库在05版本以上的都可以使用。主要用于行和列的转换。

3.1 pivot纵表转横表

select
    t2.姓名,
    t2.数学,
    t2.物理,
    t2.语文
from tb as t1
pivot (sum(分数) for 课程 in(数学,语文,物理)) as t2

pivot将原来表中 课程字段中的 数据行 数学,语文,物理 转换为列，并用sum取对应列的值。

我们只需要记住它的用法就可以了。

3.2 unpivot 横表转纵表

select
     *
 from
 grade
 unpivot (分数 for 课程 in (语文,数学,物理)) as t3

 unpivot 将 语文,数学,物理 列转为行，分数为新的一列存放对应的值。