No column name was specified for column 1 of 'd'

All we need is an easy explanation of the problem, so here it is.

I am trying to get some data from 3 tables in SQL Server, but getting an error.

I have created a fiddle with similar structure and sample data

Based on the data in tblData, I am trying to get the table id, along with a concatenated string of the column names.

id     colNames
1005   City, Name, State, EmpId
1006   City, State, Name, EmpId
1008   City, Name, EmpId, Phone, State
1009   City, Name, Phone, EmpId

What could be the issue with this query?

select tp.id tmpltID,
        stuff(( select distinct d.colName from (SELECT colOrder,
        t.Name as colName
        FROM [dbo].[tblProj] pd
         join [dbo].[tblTemp] t on pd.tblName = t.tblname
        join tblData dt on t.id=dt.tmpId
        ORDER BY colOrder 
        FOR XML PATH('')) d
        )  ,
         1,
         1,
         '') 
         from [tblTemp] tp
         GROUP BY tp.id;

I am getting these errors:

Msg 8155, Level 16, State 2, Line 20
No column name was specified for column 1 of 'd'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'colName'.

If I don’t use distinct, the column names get repeated as a tmpId could be present multiple times in tblData.

I am using Microsoft SQL Server 2016 (SP2)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You don’t need to join to tblTemp again in your sub-query. You can use APPLY operator to make your query more readable.

SELECT tp.id as tmpltID,
       STUFF ( pd.Name, 1, 2, '' )  as colNames
FROM   [tblTemp] tp
       CROSS APPLY
       (
           SELECT ', ' + pd.Name
           FROM  [dbo].[tblProj] pd
           WHERE pd.tblName = tp.tblName
           ORDER BY pd.colOrder 
           FOR XML PATH ('')
       ) pd (Name)
WHERE  pd.Name IS NOT NULL

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply