SQL Server 2019任意行列动态透视实现及JSON存储可行性问询

一、动态行转列实现方案（兼容新增设备/属性无需修改查询） #

你需要的透视效果需要先对原表做逆透视（把属性列转成行），再做透视（把设备名转成列），配合SQL Server的动态SQL即可实现完全无修改兼容新增内容：

DECLARE 
    @cols NVARCHAR(MAX),
    @attrCols NVARCHAR(MAX),
    @sql NVARCHAR(MAX)

-- 1. 动态获取所有设备名作为透视后的列
SELECT @cols = STRING_AGG(QUOTENAME(Name), ',') 
FROM device

-- 2. 动态获取所有属性列，统一转为字符串类型兼容不同数据格式
SELECT @attrCols = STRING_AGG(
    CONCAT('CAST(', QUOTENAME(name), ' AS NVARCHAR(200)) AS ', QUOTENAME(name)), 
    ','
) 
FROM sys.columns 
WHERE object_id = OBJECT_ID('device') 
AND name NOT IN ('id', 'Name')

-- 3. 拼接动态执行SQL
SET @sql = N'
SELECT Attribute, ' + @cols + N'
FROM (
    SELECT d.Name, v.Attribute, v.Value
    FROM (
        SELECT Name, ' + @attrCols + N'
        FROM device
    ) d
    UNPIVOT (
        Value FOR Attribute IN (' + STUFF(REPLACE(@attrCols, ' AS ', '],['), 1, 5, '') + N')
    ) v
) t
PIVOT (
    MAX(Value) FOR Name IN (' + @cols + N')
) p
'

-- 4. 执行查询
EXEC sp_executesql @sql

兼容逻辑说明 #

• 新增设备（表新增行）：第一步查询@cols时会自动读取所有设备名，自动加入透视列
• 新增属性字段（表新增列）：第二步查询@attrCols时会自动读取除id、Name外的所有字段，自动加入逆透视逻辑
• 不同数据类型兼容：所有属性值统一转为字符串类型，支持数值、日期、布尔、空值的正常展示

二、JSON格式存储适用性分析 #

适配优势 #

如果你的业务中属性字段变动非常频繁，经常需要新增非常规属性，不想每次都修改表结构，JSON存储是更灵活的选择：

• SQL Server 2019原生支持JSON操作，可通过JSON_VALUE、OPENJSON快速提取属性值，也可以实现上述动态透视效果
• 新增属性无需修改表结构，直接在JSON串中新增字段即可

适配劣势 #

• 单个属性的筛选、聚合、索引性能远低于结构化列，即使添加JSON索引性能仍有差距
• 没有强数据类型校验，容易出现格式错误
• 存储空间占用比结构化列更高

场景选型建议 #

• 如果属性类型相对固定，核心场景为批量查询所有设备属性做透视，优先保留现有结构化表+动态SQL的方案
• 如果属性灵活度要求极高，经常需要新增临时属性，且很少对单个属性做统计筛选，可选择JSON存储方案

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