Group_Concat with join equivalent in SQL SERVER

Question

I am having trouble in my code. I used to be on MySQL but I've migrated my codes into SQL Server.

Now I am having trouble with this GROUP_CONCAT function. I found some 'potential' equivalent but it's just not resulting on what is expected.

Here is the query:

SELECT 
  GROUP_CONCAT(c.namecheck SEPARATOR '; ') AS GROUPNAME
FROM db_name a
left JOIN db_employee b ON a.nameId = b.empID
left join db_civ c ON b.nameNum = c.civNum

I tried some. But as I've said, its does not output the result that what I'm expecting (as I countercheck the query in MySQL)

Expected output should be

-----------
|GROUPNAME|
-----------
|Jay; Ron; Jorge .... etc|
|                        |
|                        |
|                        |

Aaron Bertrand made a comparison about different options you could use — James Z
– James Z, Commented Apr 29, 2016 at 14:35

JackOfAll · Accepted Answer · 2016-04-28 07:17:33Z

3

In SQL Server, use FOR XML PATH to concatenate row values into a string.

CREATE TABLE states (id int, statename nvarchar(20))
INSERT states SELECT 1, 'Texas'
INSERT states SELECT 2, 'Florida'
INSERT states SELECT 3, 'California'

CREATE TABLE capitals (id int, cityname nvarchar(20))
INSERT capitals SELECT 1, 'Austin'
INSERT capitals SELECT 2, 'Tallahassee'
INSERT capitals SELECT 2, 'Sacramento'

--The wrapper removes the leading delimiter
SELECT STUFF((
    SELECT  DISTINCT '; ' + c.cityname  --Specify delimiter
    FROM    states s
    JOIN    capitals c ON c.id = s.id
    FOR XML PATH ('')                   --This does the concatenation
), 1, 1, '' )

Output is:

Austin; Sacramento; Tallahassee

For your example, it would be:

--The wrapper removes the leading delimiter
SELECT STUFF((
    SELECT  DISTINCT '; ' + c.namecheck  --Specify delimiter
    FROM db_name a
    left JOIN db_employee b ON a.nameId = b.empID
    left join db_civ c ON b.nameNum = c.civNum
    FOR XML PATH ('')                   --This does the concatenation
), 1, 1, '' ) AS GROUPNAME

edited Apr 28, 2016 at 7:17

answered Apr 28, 2016 at 7:09

JackOfAll

1206 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

WhiteMark Over a year ago

What is the equivalent of this code in MYSQL? is it Select group_concat(cityname) from states ?

JackOfAll Over a year ago

I'm not that familiar with MYSQL, but the last section shows the FOR XML PATH syntax with your own code. Does it give you the answer you expect?

James Z Over a year ago

@WhiteMark Be careful with FOR XML if your data contains for example &, < or >. See this question.

Collectives™ on Stack Overflow

Group_Concat with join equivalent in SQL SERVER

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related