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
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.
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 🙂