如何创建SQL Server视图实现宽表转标签式窄表结构转换

要实现宽表到(LoggedDT, TagName, TagValue)窄表的转换，且自动适配新增/删除的标签列，可通过动态SQL生成UNPIVOT视图的方式解决，无需手动枚举245个标签字段。

核心实现步骤 #

利用SQL Server系统视图sys.columns自动识别所有TagNameN格式的列，拼接动态UNPIVOT语句创建视图。以下是完整脚本：

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
DECLARE @tableName NVARCHAR(128) = N'WideTagTable'; -- 替换为你的宽表名

-- 收集所有标签列名（排除LoggedDT，仅保留TagName开头的列）
SELECT @cols = STRING_AGG(QUOTENAME(name), ', ')
FROM sys.columns
WHERE object_id = OBJECT_ID(@tableName)
  AND name <> N'LoggedDT'
  AND name LIKE N'TagName%';

-- 拼接创建/更新视图的SQL语句
SET @sql = N'
CREATE OR ALTER VIEW vw_NarrowTagTable
AS
SELECT 
    LoggedDT,
    TagName,
    TagValue
FROM ' + QUOTENAME(@tableName) + N'
UNPIVOT (
    TagValue FOR TagName IN (' + @cols + N')
) AS unpvt;
';

-- 执行动态SQL创建视图
EXEC sp_executesql @sql;

关键说明 #

1. 自动适配字段变更：每次新增或删除TagNameN列后，重新运行此脚本即可更新视图，无需手动修改视图定义。
2. 版本兼容处理：如果使用SQL Server 2016及更早版本（不支持STRING_AGG），替换列名拼接逻辑为：

-- 旧版本SQL Server列名拼接方式
SELECT @cols = STUFF((
    SELECT ', ' + QUOTENAME(name)
    FROM sys.columns
    WHERE object_id = OBJECT_ID(@tableName)
      AND name <> N'LoggedDT'
      AND name LIKE N'TagName%'
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

3. 视图使用：创建完成后，直接查询vw_NarrowTagTable即可获取窄表格式的数据，例如：

SELECT * FROM vw_NarrowTagTable WHERE LoggedDT >= '2024-01-01';

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