可以使用
PIVOT
和UNPIVOT
关系运算符将表值表达式更改为另一个表。PIVOT
通过将表达式中的一个列的唯一值转换为输出中的多列,来轮替表值表达式。PIVOT
在需要对最终输出所需的所有剩余列值执行聚合时运行聚合。 与 PIVOT 执行的操作相反,UNPIVOT
将表值表达式的列轮换为列值。
一:场景模拟
--临时表[储存基本信息]
CREATE TABLE #TSTU
(
NAME NVARCHAR(10), --姓名
COURSE NVARCHAR(10), --课程
SCORE INT --分数
)
INSERT INTO #TSTU VALUES
('张三','语文',92),
('张三','数学',90),
('张三','英语',84),
('李四','语文',93),
('李四','数学',96),
('李四','英语',89)
SELECT * FROM #TSTU
IF OBJECT_ID('tempdb..#TSTU') IS NOT NULL
DROP TABLE #TSTU
二:CASE WHEN
查询每人单科成绩,最简单的方法是CASE...WHEN,适用于列名固定的场景下。
SELECT
NAME,
MAX(CASE COURSE WHEN '语文' THEN SCORE ELSE 0 END) AS '语文',
MAX(CASE COURSE WHEN '数学' THEN SCORE ELSE 0 END) AS '数学',
MAX(CASE COURSE WHEN '英语' THEN SCORE ELSE 0 END) AS '英语'
FROM #TSTU
GROUP BY NAME
三:PIVOT
3.1:语法
SELECT * FROM TABLE PIVOT(<Aggregation Function>(column) FOR Source IN (Column Headers)) A
select * from table:需要将行数据转换成列的数据源;
pivot:关键字;
<Aggregation Function>(column):聚合函数,如Max、Avg和Sum;
Source IN (Column Headers):将成为列标题的值的列,列名之间不需要引号;
A:别名,不可省略!
3.2:固定列
SELECT * FROM #TSTU PIVOT(MAX(SCORE) FOR COURSE IN(语文,数学,英语)) A
3.3:动态列
也有一些情况,转换过去的列名是未知的,来源于查询结果的某些项目名。比如超市收银系统需要统计某段时间内,每个商品销售的金额,我们不可能把数据库中商品表的所有数据一一写上去。
可以通过关键字STUFF实现商品名称的拼接,建议在拼接的过程中,最好加上括号[]转义,项目中可能会出现+()等敏感字符,导致语法错误。
DECLARE @Items NVARCHAR(MAX) --合并组合项目
SET @Items=(SELECT STUFF((SELECT DISTINCT ',[' + ItemName +']' FROM #TDETAIL FOR XML PATH('')), 1, 1, ''))
得到合并后的项目名Items后,这时就需要用SQL字符串来执行语句了。
DECLARE @SQL NVARCHAR(MAX)
SET @SQL='SELECT * FROM #TDETAIL PIVOT (SUM(Price) FOR ItemName IN (' +@Items+')) TMP'
EXEC(@SQL)
相关资料:使用 PIVOT 和 UNPIVOT