如何使用组由连接字符串在SQL Server?

我如何得到:

id       Name       Value
1          A          4
1          B          8
2          C          9

来

id          Column
1          A:4, B:8
2          C:9
755886 次浏览
小开

这类问题在这里经常被问到，解决方案将在很大程度上取决于潜在的需求:

< a href = " https://stackoverflow.com/search?q=sql +主”> https://stackoverflow.com/search?q=sql +主< / >

而且

< a href = " https://stackoverflow.com/search?q=sql +连接”> https://stackoverflow.com/search?q=sql +连接< / >

通常，如果没有动态sql、用户定义函数或游标，就没有仅使用sql的方法来完成此任务。

小开

只是补充一下Cade所说的，这通常是一个前端显示的事情，因此应该在那里处理。我知道有时候100%用SQL编写文件导出或其他“仅SQL”解决方案更容易，但大多数时候这种连接应该在显示层中处理。

小开

不需要光标…while循环就足够了。

------------------------------
-- Setup
------------------------------


DECLARE @Source TABLE
(
id int,
Name varchar(30),
Value int
)


DECLARE @Target TABLE
(
id int,
Result varchar(max)
)




INSERT INTO @Source(id, Name, Value) SELECT 1, 'A', 4
INSERT INTO @Source(id, Name, Value) SELECT 1, 'B', 8
INSERT INTO @Source(id, Name, Value) SELECT 2, 'C', 9




------------------------------
-- Technique
------------------------------


INSERT INTO @Target (id)
SELECT id
FROM @Source
GROUP BY id


DECLARE @id int, @Result varchar(max)
SET @id = (SELECT MIN(id) FROM @Target)


WHILE @id is not null
BEGIN
SET @Result = null


SELECT @Result =
CASE
WHEN @Result is null
THEN ''
ELSE @Result + ', '
END + s.Name + ':' + convert(varchar(30),s.Value)
FROM @Source s
WHERE id = @id


UPDATE @Target
SET Result = @Result
WHERE id = @id


SET @id = (SELECT MIN(id) FROM @Target WHERE @id < id)
END


SELECT *
FROM @Target
小开
最佳答案

不需要游标，WHILE循环或用户定义函数。

只需要创造性地使用FOR XML和PATH。

[注意:此解决方案仅适用于SQL 2005及更高版本。原来的问题没有指定使用的版本。

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)


INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)


SELECT
[ID],
STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
FROM #YourTable
WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID


DROP TABLE #YourTable
小开

SQL Server 2005及以后的版本允许你创建自己的自定义聚合函数，包括像连接这样的东西——请参阅链接文章底部的示例。

小开

使用Sql Server 2005及以上版本的另一种选择

---- test data
declare @t table (OUTPUTID int, SCHME varchar(10), DESCR varchar(10))
insert @t select 1125439       ,'CKT','Approved'
insert @t select 1125439       ,'RENO','Approved'
insert @t select 1134691       ,'CKT','Approved'
insert @t select 1134691       ,'RENO','Approved'
insert @t select 1134691       ,'pn','Approved'


---- actual query
;with cte(outputid,combined,rn)
as
(
select outputid, SCHME + ' ('+DESCR+')', rn=ROW_NUMBER() over (PARTITION by outputid order by schme, descr)
from @t
)
,cte2(outputid,finalstatus,rn)
as
(
select OUTPUTID, convert(varchar(max),combined), 1 from cte where rn=1
union all
select cte2.outputid, convert(varchar(max),cte2.finalstatus+', '+cte.combined), cte2.rn+1
from cte2
inner join cte on cte.OUTPUTID = cte2.outputid and cte.rn=cte2.rn+1
)
select outputid, MAX(finalstatus) from cte2 group by outputid
小开

这只是Kevin Fairchild的文章的补充(顺便说一句，非常聪明)。我本来会把它作为一个评论，但我还没有足够的分数:)

我将这个想法用于我正在工作的视图，然而我正在连接的项目包含空间。因此，我稍微修改了代码，不再使用空格作为分隔符。

再次感谢你酷炫的解决办法，凯文!

CREATE TABLE #YourTable ( [ID] INT, [Name] CHAR(1), [Value] INT )


INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (1, 'A', 4)
INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (1, 'B', 8)
INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (2, 'C', 9)


SELECT [ID],
REPLACE(REPLACE(REPLACE(
(SELECT [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) as A
FROM   #YourTable
WHERE  ( ID = Results.ID )
FOR XML PATH (''))
, '</A><A>', ', ')
,'<A>','')
,'</A>','') AS NameValues
FROM   #YourTable Results
GROUP  BY ID


DROP TABLE #YourTable
小开

当我尝试将Kevin Fairchild的建议转换为包含空格和编码的特殊XML字符(&， <， >)的字符串时，遇到了几个问题。

我的代码的最终版本(它没有回答最初的问题，但可能对某些人有用)看起来像这样:

CREATE TABLE #YourTable ([ID] INT, [Name] VARCHAR(MAX), [Value] INT)


INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'Oranges & Lemons',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'1 < 2',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)


SELECT  [ID],
STUFF((
SELECT ', ' + CAST([Name] AS VARCHAR(MAX))
FROM #YourTable WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE
/* Use .value to uncomment XML entities e.g. &gt; &lt; etc*/
).value('.','VARCHAR(MAX)')
,1,2,'') as NameValues
FROM    #YourTable Results
GROUP BY ID


DROP TABLE #YourTable

它不是使用空格作为分隔符并将所有空格替换为逗号，而是在每个值前附加一个逗号和空格，然后使用STUFF删除前两个字符。

XML编码通过使用类型指令自动处理。

小开

使用XML路径将不能像您所期望的那样完美地连接…它会将“&”替换为“&”并且还会混淆<" and "> …也许还有其他一些事情，不确定……但是你可以试试这个

我想到了一个变通办法…您需要替换:

FOR XML PATH('')
)

:

FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)')

…或NVARCHAR(MAX)如果这是你使用的。

为什么SQL没有一个串联聚合函数?这是PITA。

小开

一个例子是

在Oracle中可以使用LISTAGG聚合函数。

原始记录

name   type
------------
name1  type1
name2  type2
name2  type3

Sql

SELECT name, LISTAGG(type, '; ') WITHIN GROUP(ORDER BY name)
FROM table
GROUP BY name

导致

name   type
------------
name1  type1
name2  type2; type3
小开

让我们变得非常简单:

SELECT stuff(
(
select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb
FOR XML PATH('')
)
, 1, 2, '')

替换这一行:

select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb

你的疑问。

小开

如果group by只包含一个项目，您可以通过以下方式显著提高性能:

SELECT
[ID],


CASE WHEN MAX( [Name]) = MIN( [Name]) THEN
MAX( [Name]) NameValues
ELSE


STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
FROM #YourTable
WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues


END


FROM #YourTable Results
GROUP BY ID
小开

从http://groupconcat.codeplex.com安装SQLCLR聚合

然后你可以像这样写代码来得到你想要的结果:

CREATE TABLE foo
(
id INT,
name CHAR(1),
Value CHAR(1)
);


INSERT  INTO dbo.foo
(id, name, Value)
VALUES  (1, 'A', '4'),
(1, 'B', '8'),
(2, 'C', '9');


SELECT  id,
dbo.GROUP_CONCAT(name + ':' + Value) AS [Column]
FROM    dbo.foo
GROUP BY id;
小开

八年后……Microsoft SQL Server vNext数据库引擎最终增强了Transact-SQL，以直接支持分组字符串连接。社区技术预览版本1.0添加了STRING_AGG函数，CTP 1.1为STRING_AGG函数添加了WITHIN GROUP子句。

参考:https://msdn.microsoft.com/en-us/library/mt775028.aspx

小开

没有看到任何交叉应用的答案，也不需要XML提取。这是凯文·费尔柴尔德的一个略有不同的版本。在更复杂的查询中使用它更快更容易:

   select T.ID
,MAX(X.cl) NameValues
from #YourTable T
CROSS APPLY
(select STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
FROM #YourTable
WHERE (ID = T.ID)
FOR XML PATH(''))
,1,2,'')  [cl]) X
GROUP BY T.ID
小开

如果是SQL Server 2017或SQL Server Vnext, SQL Azure，您可以使用STRING_AGG如下所示:

SELECT id, STRING_AGG(CONCAT(name, ':', [value]), ', ')
FROM #YourTable
GROUP BY id
小开

使用Replace函数和FOR JSON PATH

SELECT T3.DEPT, REPLACE(REPLACE(T3.ENAME,'{"ENAME":"',''),'"}','') AS ENAME_LIST
FROM (
SELECT DEPT, (SELECT ENAME AS [ENAME]
FROM EMPLOYEE T2
WHERE T2.DEPT=T1.DEPT
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER) ENAME
FROM EMPLOYEE T1
GROUP BY DEPT) T3

对于样本数据和更多方法点击这里

小开

如果你启用了clr，你可以使用GitHub中的Group_Concat库

小开

另一个不带垃圾的例子:"，TYPE).value('(./text())[1]'，'VARCHAR(MAX)')"

WITH t AS (
SELECT 1 n, 1 g, 1 v
UNION ALL
SELECT 2 n, 1 g, 2 v
UNION ALL
SELECT 3 n, 2 g, 3 v
)
SELECT g
, STUFF (
(
SELECT ', ' + CAST(v AS VARCHAR(MAX))
FROM t sub_t
WHERE sub_t.g = main_t.g
FOR XML PATH('')
)
, 1, 2, ''
) cg
FROM t main_t
GROUP BY g

输入-输出是

*************************   ->  *********************
*   n   *   g   *   v   *       *   g   *   cg      *
*   -   *   -   *   -   *       *   -   *   -       *
*   1   *   1   *   1   *       *   1   *   1, 2    *
*   2   *   1   *   2   *       *   2   *   3       *
*   3   *   2   *   3   *       *********************
*************************
小开

使用Stuff和for xml路径操作符将行连接到字符串:

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)


INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',5)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)


-- retrieve each unique id and name columns and concatonate the values into one column
SELECT
[ID],
STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) -- CONCATONATES EACH APPLICATION : VALUE SET
FROM #YourTable
WHERE (ID = Results.ID and Name = results.[name] )
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID




SELECT
[ID],[Name] , --these are acting as the group by clause
STUFF((
SELECT ', '+  CAST([Value] AS VARCHAR(MAX)) -- CONCATONATES THE VALUES FOR EACH ID NAME COMBINATION
FROM #YourTable
WHERE (ID = Results.ID and Name = results.[name] )
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS  NameValues
FROM #YourTable Results
GROUP BY ID, name


DROP TABLE #YourTable
小开

我使用了这种方法，可能更容易掌握。获取一个根元素，然后连接到具有相同ID但不是“正式”名称的选项

  Declare @IdxList as Table(id int, choices varchar(max),AisName varchar(255))
Insert into @IdxLIst(id,choices,AisName)
Select IdxId,''''+Max(Title)+'''',Max(Title) From [dbo].[dta_Alias]
where IdxId is not null group by IdxId
Update @IdxLIst
set choices=choices +','''+Title+''''
From @IdxLIst JOIN [dta_Alias] ON id=IdxId And Title <> AisName
where IdxId is not null
Select * from @IdxList where choices like '%,%'
小开

我想对所有的医护人员说:


SELECT
s.NOTE_ID
,STUFF ((
SELECT
[note_text] + ' '
FROM
HNO_NOTE_TEXT s1
WHERE
(s1.NOTE_ID = s.NOTE_ID)
ORDER BY [line] ASC
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,
1,
2,
'') AS NOTE_TEXT_CONCATINATED
FROM
HNO_NOTE_TEXT s
GROUP BY NOTE_ID

提交答案