SQL中如何将列值拼接至别名作为JSON键名？

Why Your Initial Query Fails #

You can't use string concatenation (+) directly in the AS clause to define a column alias. Column aliases must be static values in a standard SELECT statement—dynamic aliases require either dynamic SQL or constructing the JSON structure differently.

Fix for Duplicate JSON Keys (Dynamic Key Names) #

Since JSON doesn't allow duplicate keys, you need to generate unique keys like employeeTable_EmployeeID_100 for each employee. Here are two valid approaches:

Approach 1: Dynamic SQL #

This method builds the query dynamically to include unique aliases for each employee:

DECLARE @sql NVARCHAR(MAX) = ''

-- Build the SELECT clauses for each employee
SELECT @sql += CONCAT(
    'JSON_QUERY((SELECT Id, Name FROM employeeTable WHERE Id = ', CAST(Id AS VARCHAR(20)), ' FOR JSON AUTO, INCLUDE_NULL_VALUES)) AS ''employeeTable_EmployeeID_', CAST(Id AS VARCHAR(20)), ''', '
)
FROM employeeTable
WHERE Id IN (1, 2) -- Filter to target employees

-- Remove trailing comma and add JSON root
SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' FOR JSON PATH, ROOT(''Report_100'')'

-- Execute the dynamic query
EXEC sp_executesql @sql

Approach 2: Use STRING_AGG (SQL Server 2017+) #

If you prefer avoiding dynamic SQL, use STRING_AGG to construct the JSON object with dynamic keys:

SELECT 
    JSON_QUERY(
        CONCAT(
            '{',
            (
                SELECT STRING_AGG(
                    CONCAT('"employeeTable_EmployeeID_', Id, '": ', (SELECT Id, Name FROM employeeTable e2 WHERE e2.Id = e1.Id FOR JSON AUTO, INCLUDE_NULL_VALUES)),
                    ', '
                )
                FROM employeeTable e1
                WHERE Id IN (1, 2) -- Filter to target employees
            ),
            '}'
        )
    ) AS Report_100
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Both approaches will generate JSON with unique keys like employeeTable_EmployeeID_1 and employeeTable_EmployeeID_2, wrapped under the Report_100 root.

内容的提问来源于stack exchange，提问作者I Love Stackoverflow