Concatenating Rows from a table

Question

In SQL Server, I'm using STUFF, XML functionality to use this feature of concatenating strings. After the process i have to update the last element of the record.

My table looks like this:

id   category
-------------
1      Pop
1      Rock
2      Pop
3      Rock

Same ID category should get append with | delimiter

Pop|Rock| as Pop|Rock

This is the way I'm trying to do. But the Stuff and XML is killing my performance.

Any help would be appreciated.

How does your xml 'stuff' look like? Do you have an example query? — Chief Wiggum, Nov 02 '14 at 09:24
Show your code. But [this question](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) indicates that `STUFF, XML` is the way to do it. — Barmar, Nov 02 '14 at 09:27
possible duplicate of [SQL group\_concat function in SQL Server](http://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server) — Pரதீப், Nov 02 '14 at 10:03

score 1 · Accepted Answer · answered Nov 02 '14 at 13:36

Does your query look something like this?

select id,
       stuff((select '|' + category
              from table t2
              where t2.id = t.id
              for xml path ('')
             ), 1, 1, '') as categories
from (select distinct id from table t) t;

If you don't use this method, there are other methods in SQL Server, but I think they would have much worse performance.

Concatenating Rows from a table

1 Answers1