pivot与unpivot用法介绍
可以使用 PIVOT 和 UNPIVOT 关系运算符将表值表达式更改为另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式,并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作,将表值表达式的列转换为列值。
以下是带批注的 PIVOT 语法:
SELECT <非透视的列>,
[第一个透视的列] AS <列名称>,
[第二个透视的列] AS <列名称>,
…
[最后一个透视的列] AS <列名称>,
FROM
(<生成数据的 SELECT 查询>)
AS <源查询的别名>
PIVOT
(
<聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
IN ( [第一个透视的列], [第二个透视的列],
… [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;
例如:
SELECT
*
FROM
(
SELECT
'device_id' AS names,
COUNT (1) AS num
FROM
base_device_info
WHERE
device_state = 1
and device_id = #{device_id}
UNION ALL
SELECT
'ori_station' AS names,
COUNT (1) AS num
FROM
base_station_info
WHERE
station_id = #{ori_station}
UNION ALL
SELECT
'des_station' AS names,
COUNT (1) AS num
FROM
base_station_info
WHERE
station_id = #{des_station}
) PIVOT (
SUM (num) FOR names IN (
'device_id' AS device_id,
'ori_station' AS ori_station,
'des_station' AS des_station
)
)
请参考MSDN中关于PIVOT的用法:
https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms177410(v=sql.105)?redirectedfrom=MSDN
https://blog.csdn.net/timothy_mok1989/article/details/83986776
https://blog.csdn.net/qq_34409900/article/details/81907228