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