1

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

enter image description here

Camus
  • 827
  • 2
  • 20
  • 36

1 Answers1

5

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
Atk
  • 754
  • 1
  • 4
  • 12
  • 1
    @DaleK I tested the result using his and mine query. – Atk Mar 16 '20 at 06:21
  • Thanks. That did it. Do you know why without the alias it seems to retrieve everything? – Camus Mar 16 '20 at 22:11
  • 1
    @Camus In your query B.qID=qID returns always true as if 1=1, it was not doing anything. It was similar to B.qID=B.qID. – Atk Mar 17 '20 at 04:13