SQL Server动态PIVOT行转列时动态转换自定义属性数据类型

核心思路是在生成动态列的同时，基于customattributesx表中存储的每个属性的type值，动态生成每列的类型转换语句，替换PIVOT后的SELECT *写法即可实现需求，无需提前知晓自定义属性的数量和类型。

修改后的完整代码 #

DECLARE @fixedColumns NVARCHAR(MAX) = 'userid, firstname, lastname, datecreated, username, email';
DECLARE @pivotColumns NVARCHAR(MAX) = '';
DECLARE @castColumns NVARCHAR(MAX) = '';
DECLARE @sqlcmd NVARCHAR(MAX) = '';

-- 同时生成PIVOT用的列名和转换用的CAST语句
SELECT 
    @pivotColumns += QUOTENAME(fullname) + ',',
    @castColumns += 'CAST(' + QUOTENAME(fullname) + ' AS ' + type + ') AS ' + QUOTENAME(fullname) + ','
FROM customattributesx ca
ORDER BY ca.id;

-- 去掉末尾多余的逗号
SET @pivotColumns = LEFT(@pivotColumns, LEN(@pivotColumns) - 1);
SET @castColumns = LEFT(@castColumns, LEN(@castColumns) - 1);

SET @sqlcmd = '
SELECT ' + @fixedColumns + ',' + @castColumns + ' 
FROM (
    SELECT 
        u.id as userid
        ,u.firstname
        ,u.lastname
        ,u.datecreated
        ,u.username
        ,u.email
        ,ca.fullname as fullname
        ,cav.value as value
    FROM dbo.users u
    INNER JOIN customattributevaluesx cav
        ON cav.userid = u.id
    INNER JOIN customattributesx ca
        ON cav.attributeid = ca.id
) t1
PIVOT (
    MIN(value)
    FOR fullname IN ('+@pivotColumns+')
) as pivottable
';
EXECUTE (@sqlcmd)

改动说明 #

• 移除了原代码中冗余的CROSS JOIN和嵌套子查询，逻辑更简洁
• 新增@castColumns变量，动态拼接每列的类型转换逻辑，完全匹配customattributesx中存储的字段类型
• 最终查询不再使用SELECT *，而是显式指定固定字段+转换后的自定义字段，确保每个自定义属性列都是预期的正确数据类型
• 新增自定义属性后无需修改代码，自动适配新增属性的名称和类型

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