什么是列转行？
列转行指的是将数据表中的列数据转换为行数据，并且按照一定的规则进行排列，这个方法在数据处理过程中十分常用，可以帮助我们更好地理解和分析数据。比如，我们可以将横向对比的数据表转换为纵向统计的数据表，从而更加直观地观察数据。
在 SQL Server 中，我们可以使用 PIVOT 和 UNPIVOT 来实现列转行操作。
什么是 PIVOT？
PIVOT 是一种将行数据转换为列数据的操作，可以将像下面这样的数据：
name cost date
---------------------
Tom 100 2022-01-01
Tom 120 2022-01-02
Tom 80 2022-01-03
Mike 150 2022-01-01
Mike 130 2022-01-02
Mike 120 2022-01-03
转换为像下面这样的数据：
name 2022-01-01 2022-01-02 2022-01-03
-----------------------------------------
Tom 100 120 80
Mike 150 130 120
上面这个操作可以使用 PIVOT 来实现。
如何使用 PIVOT？
1. 基本语法
PIVOT 的基本语法如下：
SELECT non-pivoted column, [first pivoted column] AS column name, [second pivoted column] AS column name, ...
FROM table name
PIVOT (
aggregate function ( value column )
FOR pivot column IN ( value1, value2, ... )
) AS pivot table alias
其中，<non-pivoted column> 是指非转置的列，<pivot column> 是指需要转置的列，<value column> 是指需要 pivot 的列的数据。
举个例子：
比如对于下面这个数据表，我们需要将性别信息转换为列：
id name gender
------------------
1 Tom Male
2 Mike Male
3 Lily Female
可以使用以下语句：
SELECT name, Male, Female
FROM
(SELECT name, gender FROM table name) AS SourceTable
PIVOT
(
COUNT(gender)
FOR gender IN (Male, Female)
) AS PivotTable
这个操作的结果如下：
name Male Female
---------------------
Tom 1 0
Mike 1 0
Lily 0 1
2. 多列转换
可以使用多个 PIVOT 子句实现多列转换，例如：
SELECT non-pivoted column, [first pivoted column] AS column name, [second pivoted column] AS column name, ...
FROM table name
PIVOT (
aggregate function ( value column1 )
FOR pivot column1 IN ( value1, value2, ... )
) AS pivot table alias1
PIVOT (
aggregate function ( value column2 )
FOR pivot column2 IN ( value1, value2, ... )
) AS pivot table alias2
3. 透视表过滤
如果需要筛选透视表的数据，则可以添加 WHERE 子句。例如：
SELECT ...
FROM ...
PIVOT ...
WHERE filter condition
4. 使用动态 SQL 实现自动转换
我们可以使用动态 SQL 来实现自动转换，这样可以让代码更加灵活。
下面是一个实例：
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(age)
FROM table name
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = 'SELECT name, ' + @cols + ' from
(
select name
, age
from table name
) x
pivot
(
COUNT(age)
for age in (' + @cols + ')
) p '
EXECUTE(@query)
什么是 UNPIVOT？
UNPIVOT 是一种将列数据转换为行数据的操作，可以将像下面这样的数据：
name 2022-01-01 2022-01-02 2022-01-03
-----------------------------------------
Tom 100 120 80
Mike 150 130 120
转换为像下面这样的数据：
name date cost
------------------------
Tom 2022-01-01 100
Tom 2022-01-02 120
Tom 2022-01-03 80
Mike 2022-01-01 150
Mike 2022-01-02 130
Mike 2022-01-03 120
上面这个操作可以使用 UNPIVOT 来实现。
如何使用 UNPIVOT？
1. 基本语法
UNPIVOT 的基本语法如下：
SELECT non-pivoted column(s), pivot column, value column
FROM table name
UNPIVOT
(
value column
FOR pivot column IN ( value1, value2, ... )
) AS unpivot table alias
其中，<non-pivoted column(s)> 是指非转置的列，<pivot column> 是指需要转置的列，<value column> 是指需要 pivot 的列的数据。
举个例子：
比如对于下面这个数据表，我们需要将日期信息转换为列：
name date cost
------------------------
Tom 2022-01-01 100
Tom 2022-01-02 120
Tom 2022-01-03 80
Mike 2022-01-01 150
Mike 2022-01-02 130
Mike 2022-01-03 120
可以使用以下语句：
SELECT name, date, cost
FROM
(SELECT name, [2022-01-01], [2022-01-02], [2022-01-03] FROM table name) AS SourceTable
UNPIVOT
(
cost
FOR date IN ([2022-01-01], [2022-01-02], [2022-01-03])
) AS UnpivotTable
这个操作的结果如下：
name date cost
------------------------
Tom 2022-01-01 100
Tom 2022-01-02 120
Tom 2022-01-03 80
Mike 2022-01-01 150
Mike 2022-01-02 130
Mike 2022-01-03 120
2. 多列转换
可以使用多个 UNPIVOT 子句实现多列转换，例如：
SELECT non-pivoted column(s), pivot column1, value column1, pivot column2, value column2
FROM table name
UNPIVOT
(
value column1
FOR pivot column1 IN ( value1, value2, ... )
) AS unpivot table alias1
UNPIVOT
(
value column2
FOR pivot column2 IN ( value1, value2, ... )
) AS unpivot table alias2
3. 使用动态 SQL 实现自动转换
我们可以使用动态 SQL 来实现自动转换，这样可以让代码更加灵活。
下面是一个实例：
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
SELECT @cols = STUFF((SELECT distinct ',' + QUOTENAME(date)
FROM table name
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @query = 'SELECT name, date, cost from
(
select *
from table name
) x
UNPIVOT
(
cost
FOR date IN (' + @cols + ')
) p '
EXECUTE(@query)
结语
通过本文的介绍，相信读者已经了解了 SQL Server 中的列转行操作，包括如何使用 PIVOT 和 UNPIVOT 来实现。在实际工作中，这个操作很常用，可以帮助我们更好地统计和分析数据。