CODEWITHSUNDEEP
sql-server
atalay
atalay

Reputation: 75

How to use GROUP_CONCAT function on MSSQL

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

Upvotes: 2

Views: 21975

Answers (2)

Ark667
Ark667

Reputation: 181

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

Upvotes: 9

I Love You
I Love You

Reputation: 258

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;

Upvotes: 1

Related Questions