Relevant Relevant - 5 months ago 9
SQL Question

Problems with using STUFF

Why the heck isn't this working??? Seems to follow everything I've found around here. I'm getting the error:
Column '#TempTable.clientId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I add the tt.clientId to the group by, then it doesn't do the stuff, and combine them all into 1 line, they come up as separate rows. Did I make a typo or something?

SELECT tt.Station, STUFF((SELECT ', ' + c.client_code
FROM client c
WHERE tt.clientId = c.ID
FOR XML PATH('')),1,1,'') [Values]
FROM #TempTable tt
GROUP BY tt.Station

Answer

SELECT ... FOR XML PATH should be a function of GROUP BY column[s] tt.station in your case. Something like that

SELECT tt.Station, STUFF((SELECT ', ' + c.client_code 
    FROM client c
    JOIN  #TempTable tt2  
       ON tt2.clientId = c.ID 
       AND tt2.Station = tt.Station
    FOR XML PATH('')),1,1,'') [Values]            
FROM
GROUP BY tt.Station