如何横向关联3张表并实现数据透视？SQL查询求助

需要通过RecordID关联三张表：

• tcp_employeework.CostCode：需RecordID、FullName、Status字段
• tcp_company.CustomFieldValue：需CustomFieldRecordID、Value字段
• tcp_company.CustomField：需RecordID、Name字段

其中CustomField是CustomFieldValue的字段名称表。当前查询语句能完成数据关联，但需要将数据横向展示：每个FullName对应一行，以CustomField.Name作为列头展示对应的Value值。

当前查询语句：

SELECT 
nm.FullName,nm.Status,fs.Value,cf.Name from tcp_employeework.CostCode as nm 
right join tcp_company.CustomFieldValue as fs  on fs.CostCodeRecordId=nm.RecordId
inner join tcp_company.customfield as CF
on (fs.CustomFieldRecordId=cf.RecordId and nm.status=0)

要实现行转列的横向展示效果，确实需要用到PIVOT语法，根据自定义字段名称是否固定，分两种实现方式：

1. 自定义字段名称固定的情况 #

如果CustomField.Name的取值是已知且固定的（比如固定为「性别」「部门」「职位」这类），可以直接写死列名：

SELECT FullName, Status, [性别], [部门], [职位]
FROM (
    -- 先获取基础关联数据，过滤status=0的记录
    SELECT 
        nm.FullName,
        nm.Status,
        fs.Value,
        cf.Name
    FROM tcp_employeework.CostCode as nm
    INNER JOIN tcp_company.CustomFieldValue as fs 
        ON fs.CostCodeRecordId = nm.RecordId
    INNER JOIN tcp_company.CustomField as CF
        ON fs.CustomFieldRecordId = cf.RecordId
    WHERE nm.Status = 0
) AS SourceData
PIVOT (
    MAX(Value)  -- 每个FullName+Name对应唯一Value，用MAX/MIN均可
    FOR Name IN ([性别], [部门], [职位])  -- 替换为实际的CustomField.Name值
) AS PivotTable;

2. 自定义字段名称动态变化的情况 #

如果CustomField.Name的取值会动态新增或修改，需要用动态SQL自动生成PIVOT列：

DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- 获取所有需作为列的CustomField.Name，用引号包裹列名
SELECT @Columns = STRING_AGG(QUOTENAME(Name), ', ')
FROM tcp_company.CustomField;

-- 拼接动态SQL语句
SET @SQL = N'
SELECT FullName, Status, ' + @Columns + '
FROM (
    SELECT 
        nm.FullName,
        nm.Status,
        fs.Value,
        cf.Name
    FROM tcp_employeework.CostCode as nm
    INNER JOIN tcp_company.CustomFieldValue as fs 
        ON fs.CostCodeRecordId = nm.RecordId
    INNER JOIN tcp_company.CustomField as CF
        ON fs.CustomFieldRecordId = cf.RecordId
    WHERE nm.Status = 0
) AS SourceData
PIVOT (
    MAX(Value)
    FOR Name IN (' + @Columns + ')
) AS PivotTable;';

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

关键说明 #

• 原查询的RIGHT JOIN结合nm.Status=0条件，实际效果与INNER JOIN一致，改用INNER JOIN逻辑更清晰；
• PIVOT的聚合函数用MAX或MIN都可以，因为每个FullName和Name的组合只会对应一个Value；
• 动态SQL中的STRING_AGG适用于SQL Server 2017及以上版本，若使用更早版本，可替换为FOR XML PATH方式拼接列名。

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