If you’re getting an error that reads something like “No column name was specified for column 2 of ‘SalaryTotals’” in SQL Server, it could be that you’re running a subquery or CTE, but you’re not providing a column name for an aggregate function, or for the XML or JSON result of the CTE.

To fix this issue, use an alias to define a column name for all aggregate functions and XML/JSON outputs in the subquery or CTE. Or if it’s a CTE, you can specify the column name using the CTE syntax.

Examples of Error

Here are examples of code that produce the error.

Example 1:

WITH SalaryTotals AS
(
    SELECT 
        DepartmentID,
        SUM(Salary)
    FROM Employees
    GROUP BY DepartmentID
)
SELECT * FROM SalaryTotals;

Output:

Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 2 of 'SalaryTotals'.

Here, I’m using the SUM() aggregate function inside a CTE query without providing an alias for the aggregate function.

Example 2:

SELECT * FROM
(
    SELECT 
        DepartmentID,
        SUM(Salary)
    FROM Employees
    GROUP BY DepartmentID
) AS SalaryTotals;

Output:

Msg 1033, Level 15, State 1, Procedure vGetOrdersByDate, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Here, I’m doing the same thing, except this time it’s a subquery.

Example 3:

WITH EmployeesXML AS
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    ORDER BY Salary DESC
    FOR XML PATH('Employee'), TYPE
) 
SELECT EmployeeData.query('/Employee/FirstName')
FROM EmployeesXML;

Output:

Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 1 of 'EmployeesXML'.

In this example I’m using FOR XML to output the result of the CTE query as XML, but I haven’t given a column name for the XML output.

Example 4:

WITH EmployeesJSON AS
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    ORDER BY Salary DESC
    FOR JSON PATH, ROOT('Employees')
)
SELECT * FROM EmployeesJSON;

Output:

Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 1 of 'EmployeesJSON'.

This time the CTE outputs a JSON document, and yes, you guessed it… I haven’t named the column.

Solution

To fix this issue, we need to provide a name for the relevant column/s. We can do this via an alias, or if we’re using a CTE, we have the option of providing the name near the start of the CTE.

Let’s run through and fix each of the above examples.

Fix Example 1:

WITH SalaryTotals AS
(
    SELECT 
        DepartmentID,
        SUM(Salary) AS TotalSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT * FROM SalaryTotals;

Here, I added an alias for the SUM() function’s column. In this case I used AS TotalSalary to name it TotalSalary.

Fix Example 2:

SELECT * FROM
(
    SELECT 
        DepartmentID,
        SUM(Salary) AS TotalSalary
    FROM Employees
    GROUP BY DepartmentID
) AS SalaryTotals;

Same thing, except this was a subquery instead of a CTE.

Fix Example 3:

WITH EmployeesXML (EmployeeData) AS
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    ORDER BY Salary DESC
    FOR XML PATH('Employee'), TYPE
)
SELECT EmployeeData.query('/Employee/FirstName')
FROM EmployeesXML;

In this case we have a CTE that outputs an XML document. I provided a column name for the XML document near the top. In this case it’s the (EmployeeData) part on the first line.

Fix Example 4:

WITH EmployeesJSON (EmployeeData) AS
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    ORDER BY Salary DESC
    FOR JSON PATH, ROOT('Employees')
)
SELECT * FROM EmployeesJSON;

Same thing here, except this CTE outputs its result as JSON. In any case, I resolved the issue by adding (EmployeeData) to the first line.