用ChatGPT解决这个技术问题 Extra ChatGPT

如何使用 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
这种类型的问题在 MySQL 上用它的 GROUP_CONCAT() 聚合函数很容易解决，但在 Microsoft SQL Server 上解决它就比较麻烦了。请参阅以下 SO 问题以获取帮助：“How to get multiple records against one record based on relation?”
拥有 microsoft 帐户的每个人都应该投票支持更简单的 connect 解决方案：connect.microsoft.com/SQLServer/feedback/details/427987/…
在增强 T-SQL 之前，您可以使用此处找到的 SQLCLR 聚合作为替代：groupconcat.codeplex.com
stackoverflow.com/questions/194852/… 的副本

K
Kevin Fairchild

不需要 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
为什么不锁定临时表？
嗯。我只是讨厌它的子查询风格。 JOINS 好多了。只是不认为我可以在这个解决方案中利用它。无论如何，我很高兴看到除了我之外还有其他喜欢学习这样的东西的 SQL 笨蛋。给大家点赞：）
一种更简洁的字符串操作方式： STUFF((SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) FROM #YourTable WHERE (ID = Results.ID) FOR XML PATH ('')),1,2,'') AS NameValues
我觉得 SQLServer 很尴尬，因为这是不使用变量的唯一解决方案。
这实际上是一种解决方法而不是答案，如果您有一个非常大的查询，没有简单的 ID 列并且视图中有许多 + 连接 + 子查询 + 按条件分组怎么办？将整个查询复制粘贴到“FOR XML”子句中（对于您希望加入的每一列），这真的是 SQL Server 必须提供的最佳解决方案吗？我认为真正的答案是直到 2017 字符串连接还没有被 SQL Server 原生支持。非常失望 ：（
A
Ashley Medway

如果是 SQL Server 2017 或 SQL Server Vnext，SQL Azure 可以使用 string_agg 如下： 

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

使用 XML 路径不会像您预期的那样完美连接......它将替换“&”用“&”并且还会弄乱<" and "> ...也许还有其他一些事情，不确定...但是您可以试试这个

我遇到了一个解决方法......你需要更换： 

FOR XML PATH('')
)

和： 

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

...或NVARCHAR(MAX)，如果那是您使用的。

为什么SQL没有连接聚合函数？这是一个皮塔饼。

我已经搜索了网络，寻找不对输出进行编码的最佳方法。太感谢了！这是明确的答案 - 直到 MS 对此添加适当的支持，例如 CONCAT() 聚合函数。我所做的就是把它扔到一个返回我连接字段的外部应用中。我不喜欢在我的选择语句中添加嵌套选择。
我同意，如果不使用 Value，我们可能会遇到文本是 XML 编码字符的问题。请找到我的博客，其中涵盖了 SQL Server 中分组连接的场景。 blog.vcillusion.co.in/…
L
Liam

当我尝试将 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 编码是使用 TYPE 指令自动处理的。

c
cyberkiwi

使用 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 中的问题。这对我来说非常有用！
是否可以在带有外部应用的查询中使用它？
O
Orlando Colamatteo

从 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 路径”技巧都干净得多，而且效果很好。当 SQL CLR 函数是一个选项时，我强烈推荐它。
S
Shem Sargent

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

J
Joel Coehoorn

SQL Server 2005 及更高版本允许您创建自己的 custom aggregate functions，包括用于连接之类的内容 - 请参阅链接文章底部的示例。

不幸的是，这需要（？）使用 CLR 程序集..这是另一个需要处理的问题：-/
只是示例将 CLR 用于实际的串联实现，但这不是必需的。您可以使串联聚合函数使用 FOR XML，这样至少将来调用它会更整洁！
C
Community

一个例子是

在 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
看起来不错，但这些问题并不是关于 Oracle 的。
我明白。但是我正在为 Oracle 寻找同样的东西，所以我想我会把它放在这里给像我这样的其他人:)
@MichalB。你没有错过 inside 语法吗？例如：组内的listagg（类型，'，'）（按名称排序）？
@gregory：我编辑了我的答案。我认为我的旧解决方案过去可以使用。您建议的当前表格肯定会起作用，谢谢。
对于未来的人-您可以用自己的答案编写一个新问题，以解决不同平台等显着差异
P
Phillip

这只是对 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
C
Community

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

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

和

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

通常，如果没有动态 sql、用户定义的函数或游标，就没有纯 SQL 方法可以做到这一点。

不对。 cyberkiwi 使用 cte:s 的解决方案是纯 sql，没有任何供应商特定的骇客。
在进行问答时，我不会认为递归 CTE 具有极强的可移植性，但 Oracle 现在支持它们。最好的解决方案将取决于平台。对于 SQL Server，它很可能是 FOR XML 技术或客户 CLR 聚合。
所有问题的最终答案？ stackoverflow.com/search?q=[whatever 问题]
B
Bridge

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

分组现在是前端展示的东西？连接分组结果集中的一列有很多有效的场景。
A
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
请参阅：Bad Habits to Kick : Thinking a WHILE loop isn't a CURSOR
@marc_s 也许更好的批评是 PRIMARY KEY 应该在表变量上声明。
@marc_s 进一步检查，那篇文章是假的——几乎所有关于没有 IO 测量的性能的讨论都是如此。我确实了解了 LAG - 所以谢谢你。
U
Ullas

让我们变得非常简单： 

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

与您的查询。

j
josliber

如果 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
假设您不希望列表中出现重复的名称，您可能会也可能不会。
M
Mordechai

没有看到任何交叉应用的答案，也不需要 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 编码字符的问题
C
CJurkus

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

使用替换函数和 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

获取示例数据和更多方法click here

M
Manfred Wippel

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

S
Syzako

另一个没有垃圾的例子： ",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   *       *********************
*************************
K
Ken Lassesen

我使用了这种可能更容易掌握的方法。获取一个根元素，然后连接以选择具有相同 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 '%,%'
A
Aus_10

对于我所有的医疗保健人员： 

 
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

关注公众号,不定期副业成功案例分享
关注公众号

不定期副业成功案例分享

想领先一步获取最新的外包任务吗？

立即订阅

HuntsBot(狩猎机器人)，一站式外包任务、远程工作、产品创意分享与订阅平台，支持钉钉、飞书、企业微信、邮箱、Telegram机器人订阅。平台会以及时、稳定、可靠的技术把外包任务需求、远程工作机会、产品创意等推送给每一位订阅的用户。

简体中文 English

平台

支持

联系我们

在使用过程中有任何问题或建议，可以通过以下方式联系我们：

微信公众号: 火星来客

Email: huntsbot@xinbeitime.com

Copyright© 2022-2023 www.huntsbot.com 浙ICP备2022000860号-4 All Rights Reserved.