用MSSQL查询实现列转行的小技巧

1. 背景介绍

在日常数据处理中，经常会遇到需要将列转换成行的情况，这在数据展示和数据分析中尤其常见。虽然可以使用Excel等工具进行转换，但如果数据量庞大且需求频繁，手动转换就显得繁琐且不可维护。而在MSSQL中，可以使用一些SQL技巧快速实现列转行，本文将介绍一种小技巧实现列转行。

2. 列转行的基本原理

列转行实际上是将一行数据拆分成多行，其中每一列作为新行的一列，而新行的其他列则复制原来行的值。例如，对于下面的表：

如果将其列转行，可以得到：

可以看到，在新表中，每一行代表了原来表中的一个单元格，而新表中的Subject列则是原来的表头。

3. 使用UNPIVOT实现列转行

3.1 UNPIVOT的使用

在MSSQL中，可以使用UNPIVOT函数实现列转行操作。UNPIVOT函数可以将一列或多列数据转换成行，具体的使用方式为：

SELECT [column1], [column2], [column3], ...
FROM [table]
UNPIVOT
(
    [new_column] FOR [old_column] IN ([column2], [column3], ...)
) U

其中，[column1]是转换后的行中的固定列，[column2], [column3], ...是需要转换的列，[new_column]是转换后新生成的表示旧列的值的列，[old_column]是表示需要转换的列的列名。

3.2 示例

以上面的表为例，如果要将Subject1, Subject2, Subject3这三列转换成行形式，可以使用如下SQL语句：

SELECT ID, Class, Subject, Score 
FROM student 
UNPIVOT
(
    Score FOR Subject IN (Subject1, Subject2, Subject3)
) AS U

执行结果如下：

可以看到，UNPIVOT函数将原来的三列数据转换成了多行数据。

4. 使用CROSS APPLY实现列转行

4.1 CROSS APPLY的使用

CROSS APPLY是MSSQL中一个比较强大的函数，它可以将一个表达式应用到另一个表达式的结果集上，并返回结果集。对于列转行，CROSS APPLY可以将一行数据的多列转换成多行数据，具体的使用方式为：

SELECT [column1], [column2], [new_column] 
FROM [table]
CROSS APPLY
(
    VALUES ([old_column1], [value1]), ([old_column2], [value2]), ([old_column3], [value3]), ...
) AS C([new_column], [value])

其中，[column1]和[column2]是转换后的行中的固定列，[new_column]是表示需要转换的列的列名，[old_column1], [old_column2], [old_column3]表示需要转换的列们的列名，[value1], [value2], [value3]表示每一列的具体取值。VALUES子句中的每一行就是转换后的新行。

4.2 示例

以上面的表为例，如果要将Subject1, Subject2, Subject3这三列转换成行形式，可以使用如下SQL语句：

SELECT ID, Class, Subject, Score 
FROM student
CROSS APPLY
(
    VALUES ('Subject1', Subject1), ('Subject2', Subject2), ('Subject3', Subject3)
) AS C(Subject, Score)

执行结果与使用UNPIVOT的结果一致，这种方法的主要优势在于可以方便地添加列名和对应的列值，非常灵活。

5. 总结

通过使用UNPIVOT和CROSS APPLY函数，可以快速地将列转换成行，在数据处理和分析中非常实用。在使用时，需要注意函数的具体使用方式和参数传递的正确性。