如何获取：

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


到

id          Column
1          A:4, B:8
2          C:9


评论

通过MySQL的GROUP_CONCAT（）聚合函数可以轻松解决这种类型的问题，但是在Microsoft SQL Server上解决此问题比较麻烦。请参阅以下SO问题以寻求帮助：“如何基于关系针对一个记录获取多个记录？”
具有Microsoft帐户的每个人都应该对连接上的一种更简单的解决方案投票：connect.microsoft.com/SQLServer/feedback/details/427987/…
在增强T-SQL之前，可以使用此处找到的SQLCLR聚合作为替代：groupconcat.codeplex.com
复制了stackoverflow.com/questions/194852/…

#1 楼

无需CURSOR，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


评论

为什么一个人锁定一个临时表？

–艾米B
08年11月7日在19:33
这是我一生中见过的最酷的SQL内容。知道大型数据集是否“快速”吗？它不会像游标那样开始爬行，不是吗？我希望更多的人会投票赞成这种疯狂。

–user12861
08年7月7日在21:27
嗯我只是讨厌它的子查询风格。联接好多了。只是不要以为我可以在此解决方案中利用它。无论如何，我很高兴看到除了我之外这里还有其他喜欢学习类似SQL知识的人。你们所有人都感到荣幸:)

–凯文·费尔柴尔德（Kevin Fairchild）
08年11月7日在22:02
一种更简洁的字符串处理方式：STUFF（（SELECT'，'+ [Name] +'：'+ CAST（[Value] AS VARCHAR（MAX））FROM #YourTable WHERE（ID = Results.ID）FOR XML PATH（''）），1,2，''）AS名称值

–乔纳森·赛斯（Jonathan Sayce）
2011-10-18 10:54
只是为了说明我发现的东西。即使在不区分大小写的环境中，查询的.value部分也需要小写。我猜这是因为它是XML，区分大小写

– Jaloopa
13年7月30日在15:22

#2 楼

如果是SQL Server 2017或SQL Server Vnext，SQL Azure，则可以如下使用string_agg：
select id, string_agg(concat(name, ':', [value]), ', ')
from #YourTable 
group by id


评论

完美无瑕！

–argoo
19/12/3在10:02

#3 楼


评论

我在网上搜寻了寻找不对输出进行编码的最佳方法。非常感谢！这是绝对的答案-直到MS添加对此的适当支持，例如CONCAT（）聚合函数。我要做的就是将其放入返回我的串联字段的外部应用程序中。我不喜欢在我的选择语句中添加嵌套选择。

–MikeTeeVee
13年3月15日在19:01
我同意，在不使用Value的情况下，如果文本是XML编码的字符，我们会遇到问题。请在我的博客中找到有关SQL Server中分组串联的方案。 blog.vcillusion.co.in/…

– vCillusion
18年6月2日在22:23

#4 楼

当我尝试将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删除前两个字符。

使用TYPE指令自动处理XML编码。

#5 楼

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


评论

感谢您的输入，我始终喜欢使用CTE和递归CTE解决SQL Server中的问题。这对我来说是一件好事！

– gbdavid
2015年5月29日在8:57
是否可以在带有外部Apply的查询中使用它？

–洞中有火
15年10月28日在10:01

#6 楼

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


评论

几年前使用它时，该语法比所有“ XML Path”技巧都干净得多，并且效果很好。如果选择SQL CLR函数，则强烈建议您这样做。

–AFract
16-09-13在8:24

#7 楼

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

参考：https://msdn.microsoft.com/zh-cn/library/mt775028 .aspx

#8 楼

SQL Server 2005和更高版本允许您创建自己的自定义聚合函数，包括诸如级联之类的内容，请参见链接文章底部的示例。

评论

不幸的是，这要求（？）使用CLR程序集..这是另一个要解决的问题：-//

–user166390
2012年7月11日在20:27


仅该示例将CLR用于实际的串联实现，但这不是必需的。您可以使串联聚合函数使用FOR XML，因此至少在以后调用它会更巧妙！

– Shiv
16-09-26在4:45

#9 楼

这只是Kevin Fairchild职位的补充（顺便说一句，非常聪明）。我会将其添加为评论，但是我还没有足够的要点:)

我将这个想法用于我正在研究的视图，但是我要补充的项目包含空格。所以我稍微修改了代码以不使用空格作为定界符。

再次感谢Kevin的出色解决方法！

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


#10 楼


在Oracle中，您可以使用LISTAGG聚合函数。
原始记录
name   type
------------
name1  type1
name2  type2
name2  type3


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

结果在
name   type
------------
name1  type1
name2  type2; type3


评论

看起来不错，但是问题不是关于Oracle的。

–user12861
13年2月7日在15:43
我明白。但是我一直在为Oracle寻找相同的东西，所以我想把它放在这里给像我这样的其他人:)

– Michal B.
13年2月8日在10:36
@MichalB。您不是错过了inner语法吗？例如：listagg（type，'，'）在group（按名称排序）中？

–格雷戈里
17年2月10日在21:52
@gregory：我编辑了答案。我认为我的旧解决方案过去曾经可以使用。您建议的当前表单肯定可以使用，谢谢。

– Michal B.
17-2-15在10:57


对于未来的人-您可以使用自己的答案写一个新问题，以解决诸如平台等重大差异

– Mike M
18年3月22日在11:15

#11 楼

这种问题在这里经常问到，解决方案将在很大程度上取决于基本要求：

https://stackoverflow.com/search?q=sql+pivot

和

https://stackoverflow.com/search?q=sql+concatenate

通常，没有这种方法，没有SQL的唯一方法动态sql，用户定义函数或游标。

评论

不对。 cyberkiwi使用cte：s的解决方案是纯SQL，没有任何特定于供应商的黑客。

–BjörnLindqvist
13年7月25日在11:24
在提出问题时，我不会将递归CTE视为可移植性非常好，但是Oracle现在支持它们。最好的解决方案将取决于平台。对于SQL Server，很可能是FOR XML技术或客户CLR聚合。

–Cade Roux
13年7月25日在15:11
所有问题的最终答案？ stackoverflow.com/search?q= [任何问题]

–刘俊臣
16 Dec 8'在11:07

#12 楼

只是为了补充Cade所说的，这通常是前端显示，因此应在此处处理。我知道有时候为文件导出或其他“仅SQL”解决方案之类的事情，用SQL编写100％的内容有时会更容易，但是大多数情况下，这种串联应该在您的显示层中进行处理。

评论

分组是前端显示的东西吗？有许多有效的方案可以将分组结果集中的一列串联起来。

– MGOwen
16年8月3日，下午3:21

#13 楼

不需要游标... 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循环不是游标

– marc_s
15年3月9日在19:17
@marc_s也许更好的批评是应该在表变量上声明PRIMARY KEY。

–艾米B
2015年3月10日在2:19
@marc_s在进一步检查时，该文章是虚假的-几乎所有关于没有IO测量的性能讨论。我确实了解了LAG-非常感谢。

–艾米B
2015年3月10日在4:34

#14 楼

让我们变得非常简单：

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


与您的查询。

#15 楼

如果“分组依据”主要包含一项，则可以通过以下方式显着提高性能：

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


评论

假设您不希望列表中有重复的名称，您可能会或可能不会。

– jnm2
16年6月30日在14:35

#16 楼

没有看到任何交叉应用答案，也不需要xml提取。这与Kevin Fairchild撰写的内容略有不同。在更复杂的查询中使用起来更快，更容易：

   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


评论

如果不使用Value，我们可能会遇到文本为XML编码字符的问题

– vCillusion
18年6月2日在22:20

#17 楼

使用Replace Function和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


有关示例数据和更多方式，请单击此处

#18 楼

如果启用了clr，则可以使用来自GitHub的Group_Concat库

#19 楼

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


#20 楼

另一个没有垃圾的示例：“，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   *       *********************
*************************