在SQL SERVER中具有连接等效项的Group_Concat [英] Group_Concat with join equivalent in SQL SERVER

问题描述

我的代码有问题.我曾经在MySQL上使用，但是我已将代码迁移到SQL Server中.

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

现在，我在使用此GROUP_CONCAT函数时遇到了麻烦.我发现了一些潜在"等价物，但这并不是预期的结果.

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.

这是查询:

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

我尝试了一些.但是正如我所说的，它不会输出我期望的结果(因为我在MySQL中对查询进行了反检查)

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)

预期输出应为

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

推荐答案

在SQL Server中，使用FOR XML PATH将行值连接为字符串.

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, '' )

输出为:

Austin; Sacramento; Tallahassee

以您的示例为例，

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

这篇关于在SQL SERVER中具有连接等效项的Group_Concat的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！