如何在SQL Server中按ID展示列名与对应列值？

针对你需要根据指定Id将行数据转换为列名-值键值对的需求，提供两种可行方案：

静态方案（列固定时使用） #

如果表的列结构不会变动，直接用UNION ALL就能快速实现，写法直观且性能稳定：

-- 指定要转换的Id，这里以Id=1为例
SELECT 'address1' AS item, address1 AS value FROM @tab1 WHERE Id = 1
UNION ALL
SELECT 'city' AS item, city AS value FROM @tab1 WHERE Id = 1
UNION ALL
SELECT 'name' AS item, name AS value FROM @tab1 WHERE Id = 1
UNION ALL
SELECT 'phone' AS item, phone AS value FROM @tab1 WHERE Id = 1
-- 可选：按原列顺序排序
ORDER BY 
    CASE item 
        WHEN 'address1' THEN 1
        WHEN 'city' THEN 2
        WHEN 'name' THEN 3
        WHEN 'phone' THEN 4
    END;

如果想用UNPIVOT语法，也可以这样写：

SELECT item, value
FROM (
    SELECT 
        'address1' AS item1, CAST(address1 AS VARCHAR(200)) AS value1,
        'city' AS item2, CAST(city AS VARCHAR(200)) AS value2,
        'name' AS item3, CAST(name AS VARCHAR(200)) AS value3,
        'phone' AS item4, CAST(phone AS VARCHAR(200)) AS value4
    FROM @tab1
    WHERE Id = 1
) t
UNPIVOT (value FOR val_col IN (value1, value2, value3, value4)) up_val
UNPIVOT (item FOR item_col IN (item1, item2, item3, item4)) up_item
-- 匹配对应的列名和值
WHERE RIGHT(item_col, 1) = RIGHT(val_col, 1)
ORDER BY 
    CASE item 
        WHEN 'address1' THEN 1
        WHEN 'city' THEN 2
        WHEN 'name' THEN 3
        WHEN 'phone' THEN 4
    END;

动态方案（列可能变动时使用） #

如果表的列结构可能变化，用动态SQL自动生成查询语句，无需硬编码列名：

DECLARE @TargetId INT = 1; -- 指定要转换的Id
DECLARE @DynamicSql NVARCHAR(MAX);

-- 自动拼接列对应的查询语句
SELECT @DynamicSql = STRING_AGG(
    CONCAT('SELECT ''', COLUMN_NAME, ''' AS item, CAST(', COLUMN_NAME, ' AS VARCHAR(200)) AS value FROM @tab1 WHERE Id = ', @TargetId),
    ' UNION ALL '
)
FROM INFORMATION_SCHEMA.COLUMNS
-- 注意：如果是实体表，直接写表名；如果是表变量，需要调整获取列的方式
WHERE TABLE_NAME = 'YourActualTableName' 
    AND COLUMN_NAME NOT IN ('Id'); -- 排除Id列

-- 执行动态SQL，如果是表变量，需要传递表变量定义
EXEC sp_executesql @DynamicSql, 
    N'@tab1 table(Id int, address1 varchar(100), city varchar(50), name varchar(50), phone varchar(10))', 
    @tab1 = @tab1;

提示：如果是实体表而非表变量，动态SQL的写法会更简单，不需要传递表变量参数，直接替换@tab1为实体表名即可。

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