如何使用GROUP BY来连接SQL Server中的字符串？

如何得到:

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

至

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

Kevin Fairch.. 530

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

为什么要一个临时表？ (6认同)

一种稍微简洁的字符串操作方式:STUFF((SELECT','+ [Name] +':'+ CAST([Value] AS VARCHAR(MAX))FROM #YourTable WHERE(ID = Results.ID)FOR XML PATH('')),1,2,'')AS NameValues (6认同)

呃.我只是讨厌它的子查询样式.JOINS非常好.只是不要以为我可以在此解决方案中使用它.无论如何,我很高兴看到除了喜欢学习这类东西的我之外还有其他SQL dorks.感谢大家:) (5认同)

这是我一生中见过的最酷的SQL.不知道大数据集是否"快"？它不像光标那样开始爬行,是吗？是吗？我希望更多的人能把这种疯狂投票. (3认同)

只是要注意我发现的东西.即使在不区分大小写的环境中,查询NEEDS的.value部分也是小写的.我猜这是因为它是XML,它区分大小写 (3认同)

我发现SQLServer令人尴尬的是,如果不使用变量,这是解决此问题的唯一方法. (3认同)

---

Kannan Kanda.. 100

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

select id, string_agg(concat(name, ':', [value]), ', ')
    from #YourTable 
    group by id

---

小智.. 49

使用XML路径不会像你期望的那样完美地连接......它会将"&"替换为"&" 并且还会搞砸<" and "> ...也许还有其他一些事情,不确定......但你可以试试这个

我遇到了一个解决方法...你需要替换:

FOR XML PATH('')
)

有:

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

......或者NVARCHAR(MAX)如果那就是你正在使用的东西.

为什么地狱没有SQL连接聚合函数？这是一个PITA.

1> Kevin Fairch..：

---

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

---

为什么要一个临时表？

---

一种稍微简洁的字符串操作方式:STUFF((SELECT','+ [Name] +':'+ CAST([Value] AS VARCHAR(MAX))FROM #YourTable WHERE(ID = Results.ID)FOR XML PATH('')),1,2,'')AS NameValues

---

呃.我只是讨厌它的子查询样式.JOINS非常好.只是不要以为我可以在此解决方案中使用它.无论如何,我很高兴看到除了喜欢学习这类东西的我之外还有其他SQL dorks.感谢大家:)

---

这是我一生中见过的最酷的SQL.不知道大数据集是否"快"？它不像光标那样开始爬行,是吗？是吗？我希望更多的人能把这种疯狂投票.

---

只是要注意我发现的东西.即使在不区分大小写的环境中,查询NEEDS的.value部分也是小写的.我猜这是因为它是XML,它区分大小写

---

我发现SQLServer令人尴尬的是,如果不使用变量,这是解决此问题的唯一方法.

2> Kannan Kanda..：

---

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

select id, string_agg(concat(name, ':', [value]), ', ')
    from #YourTable 
    group by id

3> 小智..：

---

使用XML路径不会像你期望的那样完美地连接......它会将"&"替换为"&" 并且还会搞砸<" and "> ...也许还有其他一些事情,不确定......但你可以试试这个

我遇到了一个解决方法...你需要替换:

FOR XML PATH('')
)

有:

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

......或者NVARCHAR(MAX)如果那就是你正在使用的东西.

为什么地狱没有SQL连接聚合函数？这是一个PITA.

---

我已经搜索网络寻找不编码输出的最佳方法.非常感谢!这是明确的答案 - 直到MS为此添加适当的支持,如CONCAT()聚合函数.我做的是把它扔进一个返回我的连接字段的Outer-Apply.我不喜欢在我的select语句中添加嵌套选择.

4> Jonathan Say..：

---

我遇到了几个问题,当我试图转换凯文飞兆半导体的建议包含空格和特殊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. > < 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

6> Orlando Cola..：

---

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

7> Joel Coehoor..：

---

SQL Server 2005及更高版本允许您创建自己的自定义聚合函数,包括连接等内容 - 请参阅链接文章底部的示例.

---

不幸的是,这需要(？)使用CLR程序集..这是另一个需要处理的问题: - /

8> Michal B...：

---

在Oracle中,您可以使用LISTAGG聚合函数.一个例子是:

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

会导致:

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

---

我明白.但是我正在为Oracle寻找同样的东西,所以我想我会把它放在像我这样的其他人这里:)

---

看起来不错,但问题特别不是关于Oracle.

9> 小智..：

---

八年后...... 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

10> Cade Roux..：

---

这种问题经常在这里提出,解决方案将在很大程度上取决于基本要求:

/sf/ask/17360801/?q=sql+pivot

和

/sf/ask/17360801/?q=sql+concatenate

通常,如果没有动态sql,用户定义函数或游标,则不存在仅使用SQL的方法.

---

不对.使用cte:s的cyberkiwi解决方案是纯sql,没有任何特定于供应商的hackery.

11> Tom H..：

---

只是为了补充Cade所说的,这通常是一个前端展示的东西,因此应该在那里处理.我知道有时在SQL中为文件导出或其他"仅SQL"解决方案编写100%的内容会更容易,但大多数情况下,这种连接应该在显示层中处理.

---

分组现在是前端展示的东西吗？有许多有效的方案可用于连接分组结果集中的一列.

12> 小智..：

---

这只是凯文·费尔柴尔德(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 (''))
                        , '', ', ')
                ,'','')
        ,'','') AS NameValues 
FROM   #YourTable Results 
GROUP  BY ID 

DROP TABLE #YourTable 

13> Amy B..：

---

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