SQL Server动态转置单行数据：生成列名与样本数据对照表

需求说明：在SQL Server中，针对已存在的Users表，生成一张结果表——第一列显示表的所有列名，第二列显示某一行（例如最后一行）的样本数据；要求查询具备动态性，仅修改表名即可自动获取所有列名，无需硬编码。

你已实现列名提取的基础功能：

SELECT COLUMN_NAME 
FROM ENT_Layer.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Users' AND COLUMN_NAME LIKE '%'

也能动态拼接列名字符串：

DECLARE @Columns as VARCHAR(MAX)

SELECT @Columns =
    COALESCE(@Columns + ', ','') + QUOTENAME(COLUMN_NAME)
FROM (
    SELECT COLUMN_NAME
    FROM ENT_Layer.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Users' AND COLUMN_NAME LIKE '%'
) AS B

Print @Columns

完整解决方案 #

结合动态SQL与UNPIVOT可以实现需求，以下是可直接复用的代码：

-- 定义变量：修改此处的表名和行筛选逻辑即可
DECLARE @TableName NVARCHAR(128) = 'Users'
DECLARE @SchemaName NVARCHAR(128) = 'ENT_Layer'
DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @UnpivotColumns NVARCHAR(MAX)
DECLARE @SelectColumns NVARCHAR(MAX)

-- 生成需要转换的列列表：将所有列转为字符串类型，统一类型以支持UNPIVOT
SELECT @SelectColumns = COALESCE(@SelectColumns + ', ', '') + 
    'CAST(' + QUOTENAME(COLUMN_NAME) + ' AS NVARCHAR(MAX)) AS ' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName

-- 生成UNPIVOT需要的列名列表
SELECT @UnpivotColumns = COALESCE(@UnpivotColumns + ', ', '') + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName

-- 拼接动态SQL：这里以取最后一行为例（假设表有自增主键ID，可根据实际调整行筛选逻辑）
SET @DynamicSQL = N'
SELECT 
    ColumnName AS 列名,
    ColumnValue AS 样本数据
FROM (
    SELECT ' + @SelectColumns + '
    FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '
    -- 替换此处的行筛选逻辑，比如取最后一行、指定ID行等
    WHERE ID = (SELECT MAX(ID) FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ')
) AS SourceTable
UNPIVOT (
    ColumnValue FOR ColumnName IN (' + @UnpivotColumns + ')
) AS UnpivotTable'

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL

关键说明 #

1. 类型统一：因为UNPIVOT要求所有参与转换的列数据类型一致，所以用CAST将所有列转为NVARCHAR(MAX)，避免类型不兼容问题。
2. 行筛选逻辑：代码中默认通过MAX(ID)取最后一行，你可以根据表的实际结构修改这部分（比如按创建时间排序取最新行，或指定特定条件）。
3. 动态性：只需修改@TableName和@SchemaName变量，即可适配任意表，无需硬编码列名。

内容的提问来源于stack exchange，提问作者Calico