SQL动态列实现问题：从另一查询获取列标题

你当前的写法存在语法错误——无法在主查询的SELECT列表中直接嵌套返回SQL片段的子查询。要实现动态从Check_Form表获取列定义并整合到主查询，必须使用动态SQL来拼接完整的查询语句，具体步骤如下：

步骤1：修正动态列生成逻辑 #

原动态子查询里的Feild_ID = CS_001会因缺少单引号导致语法错误，需要给DATA_UID加上单引号；同时处理列名的转义问题，替换掉原语句中的"。以下是两种拼接方式：

方式1：使用STRING_AGG（SQL Server 2017+支持） #

DECLARE @DynamicColumns NVARCHAR(MAX);
SELECT @DynamicColumns = STRING_AGG(
    'MAX(CASE WHEN Feild_ID = ''' + DATA_UID + ''' THEN Feild_Value END) AS [' + Data_Text + ']',
    ', '
)
FROM Check_Form 
WHERE Form_Constraint = 'PU_CS' 
ORDER BY Data_UID ASC;

方式2：使用FOR XML PATH（兼容低版本SQL Server） #

DECLARE @DynamicColumns NVARCHAR(MAX);
SELECT @DynamicColumns = STUFF((
    SELECT ', MAX(CASE WHEN Feild_ID = ''' + DATA_UID + ''' THEN Feild_Value END) AS [' + Data_Text + ']'
    FROM Check_Form 
    WHERE Form_Constraint = 'PU_CS' 
    ORDER BY Data_UID ASC
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

步骤2：拼接完整的动态SQL语句 #

把固定列、动态列和主查询的其他部分拼接成完整的SQL字符串：

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
SELECT 
    Submission_Number, Form_Type, 
    COUNT(*) OVER() AS full_count,
    MAX(CASE WHEN Feild_ID = ''CheckName'' THEN Feild_Value END) AS Name,
    MAX(CASE WHEN Feild_ID = ''CheckAltName'' THEN Feild_Value END) AS AltName,
    MAX(CASE WHEN Feild_ID = ''CheckDate'' THEN Feild_Value END) AS Date,
    MAX(CASE WHEN Feild_ID = ''CheckTime'' THEN Feild_Value END) AS Time,
    MAX(CASE WHEN Feild_ID = ''CheckArea'' THEN Feild_Value END) AS Area,
    MAX(CASE WHEN Feild_ID = ''CheckLine'' THEN Feild_Value END) AS Line,
    MAX(CASE WHEN Feild_ID = ''FormTime'' THEN Feild_Value END) AS FormTime,
    ' + @DynamicColumns + '
FROM 
    [dbo].[Check_Core]
WHERE 
    Form_Type = ''PU_CS''
GROUP BY  
    Submission_Number, Form_Type
ORDER BY Submission_Number DESC
    OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY
';

步骤3：执行动态SQL #

用系统存储过程执行拼接好的SQL语句：

EXEC sp_executesql @SQL;

关键注意事项 #

• 若Data_Text包含空格、括号等特殊字符，建议用QUOTENAME(Data_Text)替代[' + Data_Text + ']，避免列名语法错误。
• 若Check_Form表的DATA_UID和Data_Text来自不可信输入，需额外做校验或参数化处理，防范SQL注入风险。

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