Concatenate Values in a View field

Question

I am trying to construct a view based on two fields in a table. The first field is User, the second is Group. Each User may have more than one Group, so there is a separate record for each Group the user is a member of.

I would like to create a view that has a unique list of Users in column 1, and column 2 has a concatenated list of each Group the user is a member of.

For example, if User Paella is in the ABC, DEF and JKL Groups, the view would display:

Column1  Column2

----     ------

Paella   ABC, DEF, JKL

The groups need to be separated by something other than a space.

Any assistance would be much appreciated.

Answer is ; [Xml Path][1] [1]: http://stackoverflow.com/questions/30438441/sql-server-select-multiple-records-in-one-select-statement/30438583#30438583 Check this link. — Onur Cete, Jun 10 '15 at 06:29
@OnurCete Your answer has been downvoted there and some comments explaining why. This question is also about `SQL Server` and apparently your answers were not applicable to this RDBMS. I suggest either correcting the answer based on the comments you received there. — Radu Gheorghiu, Jun 10 '15 at 06:32
Thanks for coming back and accepting the answer as correct! It's been a long time! :) — Radu Gheorghiu, Mar 07 '19 at 09:10

score 0 · Accepted Answer · answered Jun 10 '15 at 06:07

I think something in the lines of this should do it for you.

SELECT 
    T2.Column1
    , STUFF((SELECT ',' + T1.Column2
             FROM <Table> T1
             WHERE T1.Column1 = T2.Column1
             FOR XML PATH('')), 1, 1, '') 
FROM <Table> T2
GROUP BY T2.Column1

The are multiple approaches but this is the one of the most commonly used.

Concatenate Values in a View field

1 Answers1