SQL Server：Inner Join结合Pivot实现数据列转行需求问询

静态Pivot（列名固定场景） #

如果Table3中的列名是固定值（比如已知要转成IsActive、IsValid、IsArchived），可以直接写静态查询：

方法1：用CASE表达式实现转置 #

SELECT 
    t1.iFirstID,
    t1.fkSomeID,
    t1.cText,
    t1.bStatus,
    IsActive = MAX(CASE WHEN o.cColumnName = 'IsActive' THEN t2.bSomeBool END),
    IsValid = MAX(CASE WHEN o.cColumnName = 'IsValid' THEN t2.bSomeBool END),
    IsArchived = MAX(CASE WHEN o.cColumnName = 'IsArchived' THEN t2.bSomeBool END)
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.iFirstID = t2.fkFirstID
LEFT JOIN Table3 o ON t2.fkOtherID = o.iOtherID
GROUP BY t1.iFirstID, t1.fkSomeID, t1.cText, t1.bStatus;

方法2：用SQL标准PIVOT关键字 #

SELECT 
    t1.iFirstID,
    t1.fkSomeID,
    t1.cText,
    t1.bStatus,
    [IsActive],
    [IsValid],
    [IsArchived]
FROM Table1 t1
LEFT JOIN (
    SELECT t2.fkFirstID, o.cColumnName, t2.bSomeBool
    FROM Table2 t2
    JOIN Table3 o ON t2.fkOtherID = o.iOtherID
) src
PIVOT (
    MAX(bSomeBool)
    FOR cColumnName IN ([IsActive], [IsValid], [IsArchived])
) pivoted ON t1.iFirstID = pivoted.fkFirstID;

动态Pivot（列名不固定场景） #

如果Table3中的列名可能随时变化，用动态SQL自动获取列名并生成查询：

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 从Table3拼接所有要转置的列名（带引号避免语法错误）
SELECT @columns = STRING_AGG(QUOTENAME(cColumnName), ', ')
FROM Table3;

-- 构建完整的动态查询语句
SET @sql = N'
SELECT 
    t1.iFirstID,
    t1.fkSomeID,
    t1.cText,
    t1.bStatus,
    ' + @columns + N'
FROM Table1 t1
LEFT JOIN (
    SELECT t2.fkFirstID, o.cColumnName, t2.bSomeBool
    FROM Table2 t2
    JOIN Table3 o ON t2.fkOtherID = o.iOtherID
) src
PIVOT (
    MAX(bSomeBool)
    FOR cColumnName IN (' + @columns + N')
) pivoted ON t1.iFirstID = pivoted.fkFirstID;
';

-- 执行动态SQL
EXEC sp_executesql @sql;

关键说明 #

• 由于Table2同一fkFirstID下无重复fkOtherID，用MAX()聚合函数不会丢失数据（每个分组对应列仅一个值）。
• 用LEFT JOIN可保留Table1所有行，若Table2无对应数据，转置列会显示NULL，需要默认值可加ISNULL(列名, 默认值)处理。

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