CODEWITHSUNDEEP
sqlsql-server
Yohanes Erwin
Yohanes Erwin

Reputation: 95

How to use where inside STUFF sql server?

So i want to display multiple row data into one column, and i found that using stuff on sql server can do it.

but i got stuck on how to use where with parent field inside the stuff query.

i tried using the parent field with the same name but i got an error, and i also tried using aliases on the parent field but same error.

SELECT 
id_collector, 
branch 
= STUFF(
    (
    SELECT 
    ', '+ a.group_branch_id
    from AMAS.dbo.tbl_area_collector a
    where id_collector = id_collector
    for XML path('')), 1, 1, ''
      )
from AMAS.dbo.tbl_area_collector GROUP BY id_collector

Upvotes: 1

Views: 347

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Qualify all column references in your query! Then your queries are more likely to work the first time:

SELECT tac.id_collector, 
       STUFF( (SELECT ', ' + tac2.group_branch_id
               FROM AMAS.dbo.tbl_area_collector tac2
               WHERE tac2.id_collector = tac.id_collector
               FROM XML PATH('')
              ), 1, 1, ''
            ) as branches
FROM AMAS.dbo.tbl_area_collector tac
GROUP BY tac.id_collector;

Upvotes: 1

Related Questions