Convert Multiple Rows into One - SQL

Question

I'm trying to have the 'test' column in the same row. I'm using Stuff() however, it seems that the 'test' column is going through all the qID What am I missing?

SELECT DISTINCT qID,
                STUFF((
                select ',' + B.text
                from tbl B
                where B.qID=qID
                order by B.text
                for xml path('')
                ),1,1,'') as test
FROM tbl 
WHERE qID in (2258060,2296222)
GROUP BY qID

Answer 1

You were missing alias on table

1. Condition B.qID=qID returns always true as if 1=1, it was not doing anything. It was similar to B.qID=B.qID.

By using alias:

SELECT DISTINCT qID,
                STUFF((
                select ',' + B.text
                from tbl B
                where B.qID=A.qID
                order by B.text
                for xml path('')
                ),1,1,'') as test
FROM tbl A
WHERE qID in (2258060,2296222)
GROUP BY qID

Its also possible without alias on outer query, by using the table name itself.

SELECT DISTINCT qID,
                STUFF((
                select ',' + B.text
                from tbl B
                where B.qID=tbl.qID --Table name before qid here 
                order by B.text
                for xml path('')
                ),1,1,'') as test
FROM tbl
WHERE qID in (2258060,2296222)
GROUP BY qID