关联两表后使用Pivot行转列报错，求正确实现方案

你的代码主要有几个关键问题，既导致了“列不存在”的报错，也没法得到预期结果：

1. 子查询字段匹配错误：你在子查询里只取了ColumnNameID和ColumnValue，但实际上需要关联后拿到ColumnNames.Name（也就是要转成列名的目标字段），而不是用ColumnNameID来做转列依据。
2. Pivot聚合对象错误：你聚合的是max(ColumnNameID)，但我们真正需要聚合的是ColumnValues.value——毕竟最终要展示的是每个列对应的具体用户数据。
3. 缺少用户分组标识：没有一个字段来区分不同的用户（比如Linda、Remil、Ash），导致Pivot无法将同一用户的多行记录合并成一行。

正确的实现代码 #

我们需要先给每个用户生成唯一的分组ID，再关联两表拿到列名和对应值，最后执行Pivot转换：

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- 第一步：获取要转成列名的所有活跃字段（即ColumnNames.Name）
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(Name) 
                      FROM tm.ColumnNames 
                      WHERE Status = 'Active'
                      ORDER BY ID
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- 第二步：构建动态Pivot查询
SET @query = '
WITH UserGroups AS (
    -- 给每个用户生成唯一分组ID：每4条记录对应一个完整用户（Fullname/Email/Position/Category各一条）
    SELECT 
        cv.ID,
        cv.ColumnNameID,
        cv.value,
        cn.Name,
        -- 计算分组ID，确保同一用户的所有记录归为一组
        UserID = ((cv.ID - 1) / 4) + 1
    FROM tm.ColumnValues cv
    INNER JOIN tm.ColumnNames cn ON cv.ColumnNameID = cn.ID
    WHERE cv.Status = ''Active'' AND cn.Status = ''Active''
)
SELECT ' + @cols + '
FROM UserGroups
PIVOT (
    -- 聚合value，取每个分组+列名对应的唯一值
    MAX(value)
    -- 按列名（Name）完成行转列
    FOR Name IN (' + @cols + ')
) AS PivotTable;';

-- 执行动态查询
EXECUTE(@query);

代码细节解释 #

1. 生成用户分组ID：通过((cv.ID - 1) / 4) + 1计算每个用户的唯一标识，因为你的ColumnValues里每4条记录刚好对应一个完整的用户信息，这样能确保同一用户的所有字段被归为一组。
2. 动态列名拼接：用STUFF和FOR XML PATH把所有活跃的列名拼接成Pivot需要的格式，QUOTENAME用来处理列名中的特殊字符，避免语法错误。
3. Pivot核心逻辑：Pivot会自动按UserID（非聚合、非转列的字段）分组，聚合每个分组下对应列名的value，最终把行数据转成你想要的列结构。

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