SQL实现保留3个ID列、逆透视其余动态列的方法求助

你当前代码存在两个核心问题：

1. 硬编码COLUMN_ID筛选待转换列，后续表结构变动后需要手动修改，无法自动适配
2. UNPIVOT操作要求所有IN子句中的列数据类型完全一致，多列类型不同时就会报错

修改后完整代码 #

DECLARE 
@SQLSTRING NVARCHAR(MAX),
@COLUMNLIST NVARCHAR(MAX) = N'',
@CONVERTED_COLUMNLIST NVARCHAR(MAX) = N''

-- 拼接两类列：原始列名列表（给UNPIVOT的IN子句用）、转类型后的列列表（给查询主体用）
SELECT
@COLUMNLIST += QUOTENAME(NAME) + N',',
@CONVERTED_COLUMNLIST += N'CONVERT(NVARCHAR(MAX), ' + QUOTENAME(NAME) + N') AS ' + QUOTENAME(NAME) + N','
FROM
sys.columns
WHERE 
OBJECT_ID = OBJECT_ID('xp.XPROPERTYVALUES') 
-- 直接排除要保留的3个ID列，不需要硬编码COLUMN_ID
AND NAME NOT IN ('id','item_id','itemtype_id')

-- 去掉末尾多余的逗号
SELECT 
@COLUMNLIST = LEFT(@COLUMNLIST, LEN(@COLUMNLIST)-1),
@CONVERTED_COLUMNLIST = LEFT(@CONVERTED_COLUMNLIST, LEN(@CONVERTED_COLUMNLIST)-1)

SET 
@SQLSTRING = 
N'
SELECT 
upv.id,
upv.item_id,
upv.itemtype_id,
upv.X_Category,
upv.X_Values
FROM 
(
    -- 先把3个保留列+统一转类型后的待Unpivot列查出来
    SELECT 
        id,
        item_id,
        itemtype_id,
        ' + @CONVERTED_COLUMNLIST + N'
    FROM xp.XPROPERTYVALUES
) AS t
UNPIVOT
(
X_Values FOR X_Category 
IN 
(' + @COLUMNLIST + N')
) AS upv
'

PRINT (@SQLSTRING)
EXECUTE sp_executesql @SQLSTRING

关键修改说明 #

• 改用列名排除要保留的ID列，后续表新增列时不需要修改代码就能自动识别待Unpivot列
• 提前将所有待Unpivot的列统一转换为NVARCHAR(MAX)类型，解决UNPIVOT要求列类型一致的问题，所有数值、日期、字符串类型都可以兼容转换为该类型
• 将@COLUMNLIST的长度调整为NVARCHAR(MAX)，避免列数过多时字符串被截断导致语法错误
• 内层先做类型转换再做UNPIVOT，逻辑更稳定，不会因为源表列类型变动报错

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