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

Atk · Accepted Answer · 2020-03-17 04:20:07Z

5

You were missing alias on table

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

edited Mar 17, 2020 at 4:20

answered Mar 16, 2020 at 6:04

Atk

7521 gold badge4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Atk Over a year ago

@DaleK I tested the result using his and mine query.

Camus Over a year ago

Thanks. That did it. Do you know why without the alias it seems to retrieve everything?

Atk Over a year ago

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

Collectives™ on Stack Overflow

Convert Multiple Rows into One - SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related