How to use GROUP_CONCAT function on MSSQL

Question

How can I use the GROUP_CONCAT function on MSSQL while MySQL is running?

current table;
     QUESTION_ID    ANSWER_ID   USER
1.        1             1       A
2.        1             1       B
3.        1             2       C

i need;

     QUESTION_ID    ANSWER_ID   USER
1.        1             1       A, B
2.        1             2       C

thanks in advance..

Possible duplicate of [How to use GROUP BY to concatenate strings in MySQL?](https://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql) — Marian Nasry, Oct 10 '17 at 08:06
Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — TT., Oct 10 '17 at 08:07
view this [same question](https://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql) , i think this is the same — Marian Nasry, Oct 10 '17 at 08:08

score 5 · Answer 1 · edited May 19 '21 at 02:39

5

The equivalent function could be STRING_AGG for SQL Server 2017 and later. Note that this function not support DISTINCT filtering.

select QUESTION_ID, ANSWER_ID, STRING_AGG(USER, ', ') as USER
from t1
group by QUESTION_ID, ANSWER_ID

https://learn.microsoft.com/en-gb/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

edited May 19 '21 at 02:39

Andrew

95
2
12

answered Jun 22 '18 at 00:38

Ark667

141
3
4

score 1 · Accepted Answer · answered Oct 10 '17 at 08:12

Try:

select distinct t1.QUESTION_ID, t1.ANSWER_ID
  STUFF((SELECT distinct '' + t2.USER
         from yourtable t2
         where t1.ANSWER_ID= t2.ANSWER_ID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,0,'') data
from yourtable t1;

How to use GROUP_CONCAT function on MSSQL

2 Answers2

Linked

Related