1. 简介
在数据处理的过程中，我们经常需要对数据进行行列转换，以满足不同的需求。在MSSQL中，可以使用多种方法来进行行列转换，包括使用PIVOT和UNPIVOT关键字、使用CASE语句等。本文将介绍在MSSQL中如何使用行列转换技术来让数据更加完美。
2. PIVOT关键字
2.1 PIVOT的基本用法
PIVOT关键字可以将行转换为列，用法如下：
SELECT *
FROM (
SELECT [Category], [Amount]
FROM [Sales]
) p
PIVOT (
SUM([Amount])
FOR [Category] IN ([A], [B], [C])
) AS pvt
上述代码中，我们首先从Sales表中选择Category和Amount两列数据。然后将这些数据作为子查询中的p表，在p表上使用PIVOT函数进行行列转换。在PIVOT函数中，我们指定了SUM([Amount])，这意味着对于每个Category，我们将使用SUM函数对Amount进行求和。而FOR子句指定了将Category列转换为新的列名，即[A], [B], [C]。
2.2 动态列名
在实际应用中，我们可能需要在运行时动态地指定PIVOT函数中的列名。在这种情况下，我们可以使用动态SQL来构建PIVOT语句。例如：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @cols = STUFF((
SELECT DISTINCT ',' + QUOTENAME([Category])
FROM [Sales]
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SET @query = '
SELECT *
FROM (
SELECT [Category], [Amount]
FROM [Sales]
) p
PIVOT (
SUM([Amount])
FOR [Category] IN (' + @cols + ')
) AS pvt
'
EXECUTE(@query)
上述代码中，我们首先使用STUFF函数和FOR XML PATH语句动态生成了@cols变量。然后使用动态SQL将@cols变量插入到PIVOT语句中，从而实现了动态列名。
3. UNPIVOT关键字
3.1 UNPIVOT的基本用法
与PIVOT关键字相反，UNPIVOT可以将列转换为行。使用UNPIVOT关键字时，我们需要指定需要转换的列名。例如：
SELECT [Year], [Quarter], [Month], [MonthValue]
FROM (
SELECT [Year], [Q1], [Q2], [Q3], [Q4]
FROM [Sales]
) p
UNPIVOT (
[MonthValue] FOR [Quarter] IN ([Q1], [Q2], [Q3], [Q4])
) AS unpvt
在上述代码中，我们首先从Sales表中选择了Year、Q1、Q2、Q3和Q4这五列数据。然后将这些数据作为子查询中的p表，在p表上使用UNPIVOT函数进行列行转换。在UNPIVOT函数中，我们指定了[MonthValue]，这意味着对于每个Quarter，我们将Q1、Q2、Q3、Q4这四列数据合并为一列MonthValue。而FOR子句同样指定了需要转换的列名。
3.2 动态列名
同样地，在使用UNPIVOT关键字时，我们也可能需要动态生成需要转换的列名。在这种情况下，我们可以使用和PIVOT关键字类似的方法来解决。例如：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @cols = STUFF((
SELECT ',' + QUOTENAME(c.name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('Sales')
AND c.name NOT IN ('Year')
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
SET @query = '
SELECT [Year], [Quarter], [Month], [MonthValue]
FROM (
SELECT [Year], ' + @cols + '
FROM [Sales]
) p
UNPIVOT (
[MonthValue] FOR [Quarter] IN (' + @cols + ')
) AS unpvt
'
EXECUTE(@query)
在上述代码中，我们使用sys.columns表和XML PATH语句生成了需要转换的列名。然后将这些列名保持到@cols变量中，使用动态SQL在UNPIVOT函数中实现动态列名。
4. CASE语句
4.1 将多列数据合并为一列
有时候，我们需要将多个列的值合并为一个列。在这种情况下，我们可以使用CASE语句来实现。例如：
SELECT [Year],
CASE
WHEN [Q1] IS NOT NULL THEN 'Q1'
WHEN [Q2] IS NOT NULL THEN 'Q2'
WHEN [Q3] IS NOT NULL THEN 'Q3'
WHEN [Q4] IS NOT NULL THEN 'Q4'
END AS [Quarter],
CASE
WHEN [Q1] IS NOT NULL THEN [Q1]
WHEN [Q2] IS NOT NULL THEN [Q2]
WHEN [Q3] IS NOT NULL THEN [Q3]
WHEN [Q4] IS NOT NULL THEN [Q4]
END AS [Amount]
FROM [Sales]
上述代码中，我们使用CASE语句将Q1、Q2、Q3和Q4这四列数据合并为一列Quarter，并将其对应的Amount提取到了Amount列中。
4.2 PIVOT和CASE的结合
在实际应用中，我们可能需要将PIVOT和CASE两种技术结合使用。例如：
SELECT [Year], [Quarter],
SUM(CASE [Category]
WHEN 'A' THEN [Amount]
ELSE 0
END) AS A,
SUM(CASE [Category]
WHEN 'B' THEN [Amount]
ELSE 0
END) AS B,
SUM(CASE [Category]
WHEN 'C' THEN [Amount]
ELSE 0
END) AS C
FROM (
SELECT [Year], [Category], [Amount]
FROM [Sales]
) p
PIVOT (
SUM([Amount])
FOR [Category] IN ([A], [B], [C])
) AS pvt
GROUP BY [Year], [Quarter]
上述代码中，我们首先从Sales表中选择了Year、Category和Amount这三列数据。然后在子查询中使用CASE语句将Category列合并为不同的列名（即[A]、[B]、[C]）。最后在PIVOT函数中使用SUM函数计算每个Year和Quarter下的Amount。
5. 结语
行列转换是数据处理中经常要使用的技术。在MSSQL中，我们可以使用多种方法来进行行列转换，包括使用PIVOT和UNPIVOT关键字、使用CASE语句等。在实际应用中，我们可以根据需要选择不同的方法来实现行列转换，以便让数据更加完美。