Convert GROUP_CONCAT statement into SQL Server

Question

I am trying to convert the following query into SQL Server from MySQL:

SELECT name, GROUP_CONCAT(DISTINCT language)
FROM table GROUP BY name

It seems like STRING_AGG() is the related function name in SQL Server, but I haven't yet been able to write the correct query.

How would this be properly be done?

Update: I believe I may not have access to that function (pre 2017). How would I then do something like:

SELECT name, (select language from table ??)
FROM table GROUP BY name

You can try with `stuff` function in SQL server. – jishan siddique May 04 '20 at 02:24 — jishan siddique, May 04 '20 at 02:24
similar questions asked many times before. – Mitch Wheat May 04 '20 at 03:18 — Mitch Wheat, May 04 '20 at 03:18

score 1 · Accepted Answer · answered May 04 '20 at 03:08

Can you please try this.

DECLARE @Table TABLE
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(50),
    language VARCHAR(50)
)


INSERT INTO @Table VALUES('A','en'),('b','nl'),('c','de'),('A','nl')

SELECT
      name
     ,STUFF((
          SELECT ',' + T.language
          FROM @Table T
          WHERE T.name = tb.name 
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS [lang]
FROM @Table tb
GROUP BY tb.name

Output be like

name       lang
---------- -----------------------
A          en,nl
b          nl
c          de

Convert GROUP_CONCAT statement into SQL Server

1 Answers1