Simulating group_concat MySQL Function in Microsoft SQL Server 2005

Simulating group_concat MySQL function in Microsoft SQL Server 2005?

No REAL easy way to do this. Lots of ideas out there, though.

Best one I've found:

SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_names
FROM information_schema.columns AS extern
CROSS APPLY
(
    SELECT column_name + ','
    FROM information_schema.columns AS intern
    WHERE extern.table_name = intern.table_name
    FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY table_name, column_names;

Or a version that works correctly if the data might contain characters such as <

WITH extern
     AS (SELECT DISTINCT table_name
         FROM   INFORMATION_SCHEMA.COLUMNS)
SELECT table_name,
       LEFT(y.column_names, LEN(y.column_names) - 1) AS column_names
FROM   extern
       CROSS APPLY (SELECT column_name + ','
                    FROM   INFORMATION_SCHEMA.COLUMNS AS intern
                    WHERE  extern.table_name = intern.table_name
                    FOR XML PATH(''), TYPE) x (column_names)
       CROSS APPLY (SELECT x.column_names.value('.', 'NVARCHAR(MAX)')) y(column_names) 

Group_concat MySQL function's equivalent in MS-SQL SERVER

We don't have such group_concat of mysql in sql server, either you can use Stuff function or for xml path functionality to achiev

Convert GROUP_CONCAT statement into SQL Server

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

How to use GROUP_CONCAT function on MSSQL

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 make a query with group_concat in sql server

Query:

SELECT
      m.maskid
    , m.maskname
    , m.schoolid
    , s.schoolname
    , maskdetail = STUFF((
          SELECT ',' + md.maskdetail
          FROM dbo.maskdetails md
          WHERE m.maskid = md.maskid
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM dbo.tblmask m
JOIN dbo.school s ON s.ID = m.schoolid
ORDER BY m.maskname

Additional information:

String Aggregation in the World of SQL Server

---

Related Topics