Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Equivalent MySQL group_concat in SQL Server

Posted on 2015-02-22
Medium Priority
492 Views
Hi,
My table looks like this:
Field1      Field2
1              A
1              B
1              C
2              A
2              B
With this mysql command select field1, group_concat(field2) as field2 from t1 Group by field1, I could achieve this:
Field1   Field2
1            A,B,C
2            A,B
How can I achieve this SQL Server 2008 R2
Regards
0
Question by:Omer-Pitou
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 18

Expert Comment

ID: 40624537
It can be done in MSSQL with a little more effort, using a recursive CTE (common table expression). The code below was originally written by Quassnoi and posted on his ExplainExtended.com blog
``````WITH
qs(Field1,Field2, rn, cnt) AS
(
SELECT  Field1,Field2,
ROW_NUMBER() OVER (PARTITION BY Field1 ORDER BY Field2),
COUNT(*) OVER (PARTITION BY Field1)
FROM    Table1
),
t (Field1,Field2, gc, rn, cnt) AS
(
SELECT  Field1,Field2,
CAST(Field2 AS NVARCHAR(MAX)), rn, cnt
FROM    qs
WHERE   rn = 1
UNION ALL
SELECT  qs.Field1, qs.Field2,
CAST(t.gc + ', ' + qs.Field2 AS NVARCHAR(MAX)),
qs.rn, qs.cnt
FROM    t
JOIN    qs
ON      qs.Field1 = t.Field1
AND qs.rn = t.rn + 1
)
SELECT  Field1, gc as Field2
FROM    t
WHERE   rn = cnt
order by Field1
OPTION (MAXRECURSION 0)
``````

See it working here on SQL Fiddle
0

LVL 49

Accepted Solution

PortletPaul earned 2000 total points
ID: 40625364
the T-SQL "FOR XML PATH" is the closest equivalent to group_concat() I believe

``````SELECT DISTINCT
T1.Field1, CA.value
FROM Table1 T1
CROSS APPLY (
SELECT
STUFF(
(
SELECT
', ' + T2.Field2
FROM Table1 AS T2
WHERE T2.Field1 = T1.Field1
ORDER BY T2.Field2
FOR XML PATH ('')
)
, 1, 1, '')
) ca (value)
;
``````
Lines 7 to 14 relate to FOR XML PATH
Lines 5, 6 & 15 related to STUFF() which is simply used here to remove the first comma in the concatenated string
Note the use of CROSS APPLY which allows correlated subqueries into the FROM clause

see a demo here: http://sqlfiddle.com/#!3/5358b/3
execution plan:  http://sqlfiddle.com/#!3/5358b/3/0

details
``````**MS SQL Server 2008 Schema Setup**:

CREATE TABLE Table1
([Field1] int, [Field2] varchar(1))
;

INSERT INTO Table1
([Field1], [Field2])
VALUES
(1, 'A'),
(1, 'B'),
(1, 'C'),
(2, 'A'),
(2, 'B'),
(3, 'Z'),
(3, 'Y'),
(3, 'T'),
(3, 'S')
;

**Query 1**:

SELECT DISTINCT
T1.Field1, CA.value
FROM Table1 T1
CROSS APPLY (
SELECT
STUFF(
(
SELECT
', ' + T2.Field2
FROM Table1 AS T2
WHERE T2.Field1 = T1.Field1
ORDER BY T2.Field2
FOR XML PATH ('')
)
, 1, 1, '')
) ca (value)

**[Results][2]**:

| FIELD1 |       VALUE |
|--------|-------------|
|      1 |     A, B, C |
|      2 |        A, B |
|      3 |  S, T, Y, Z |

[1]: http://sqlfiddle.com/#!3/5358b/3

[2]: http://sqlfiddle.com/#!3/5358b/3/0
``````
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
###### Suggested Courses
Course of the Month8 days, 20 hours left to enroll