SQL Server纵表与横表相互转换的方法

SQL Server中，纵表与横表转换可通过UNION、PIVOT和UNPIVOT操作实现，便于数据在不同格式间灵活变换。

SQL Server中纵表与横表相互转换的技巧与实现方法

在SQL Server数据库中，我们经常遇到数据存储在纵表（Column-oriented）和横表（Row-oriented）两种形式，纵表通常用于存储具有相同属性类型的数据，统计数据、日志信息等；而横表则适用于存储不同属性类型的数据，如：用户信息、商品详情等，在实际应用中，我们可能会遇到需要将纵表转换为横表，或者将横表转换为纵表的需求，本篇文章将详细介绍SQL Server中纵表与横表相互转换的方法。

纵表转横表

1、使用动态SQL

当我们知道纵表的结构时，可以使用动态SQL来实现纵表转横表，以下是一个示例：

（1）创建示例纵表：

CREATE TABLE ZongTable
(
    ID INT,
    Year INT,
    Value INT
)
GO
INSERT INTO ZongTable VALUES (1, 2018, 100)
INSERT INTO ZongTable VALUES (1, 2019, 150)
INSERT INTO ZongTable VALUES (1, 2020, 200)
GO

（2）编写动态SQL：

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT ID, '
SELECT @SQL = @SQL + N'[' + CAST(Year AS NVARCHAR(10)) + '] AS [' + CAST(Year AS NVARCHAR(10)) + '], '
FROM ZongTable
GROUP BY Year
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + N' FROM ZongTable PIVOT (SUM(Value) FOR Year IN ('
SELECT @SQL = @SQL + N'[' + CAST(Year AS NVARCHAR(10)) + '], '
FROM ZongTable
GROUP BY Year
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + N')) AS PvtTable'
EXEC sp_executesql @SQL

2、使用PIVOT函数

当纵表的列数量固定时，可以直接使用PIVOT函数进行纵表转横表：

SELECT ID,
    [2018] AS [2018],
    [2019] AS [2019],
    [2020] AS [2020]
FROM ZongTable
PIVOT (SUM(Value) FOR Year IN ([2018], [2019], [2020])) AS PvtTable

横表转纵表

1、使用UNION ALL

当横表的结构已知时，可以通过UNION ALL将横表转换为纵表：

CREATE TABLE HongTable
(
    ID INT,
    Year INT,
    Value INT
)
GO
INSERT INTO HongTable VALUES (1, 2018, 100)
INSERT INTO HongTable VALUES (1, 2019, 150)
INSERT INTO HongTable VALUES (1, 2020, 200)
GO
SELECT ID, 2018 AS Year, Value FROM HongTable WHERE Year = 2018
UNION ALL
SELECT ID, 2019 AS Year, Value FROM HongTable WHERE Year = 2019
UNION ALL
SELECT ID, 2020 AS Year, Value FROM HongTable WHERE Year = 2020

2、使用动态SQL

当横表的列数量不固定时，可以使用动态SQL来实现横表转纵表：

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N''
SELECT @SQL = @SQL + N'SELECT ID, ' + QUOTENAME(Year) + N' AS Year, ' + QUOTENAME(Year) + N' AS Value FROM HongTable WHERE Year = ' + CAST(Year AS NVARCHAR(10)) + N'
UNION ALL '
FROM HongTable
GROUP BY Year
SET @SQL = LEFT(@SQL, LEN(@SQL) - 10)
EXEC sp_executesql @SQL

本文介绍了SQL Server中纵表与横表相互转换的几种方法，包括使用动态SQL、PIVOT函数、UNION ALL等，这些方法在实际应用中具有较高的实用价值，可以根据具体需求选择合适的方法进行数据转换，需要注意的是，使用动态SQL时，要注意防范SQL注入风险，确保数据安全。

在实际项目中，我们还需要根据实际情况对纵表和横表进行优化，索引优化、查询优化等，还可以通过编写存储过程、函数等封装纵表与横表转换的逻辑，提高代码的复用性和可维护性。

掌握纵表与横表相互转换的方法，有助于我们更好地应对各种数据存储和查询需求，提高SQL Server数据库的性能和效率，希望本文能对您在实际工作中有所帮助。