如何使用组来连接SQL服务器中的字符串?

How do I get:

我怎么得到:

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

to

来

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

16 个解决方案

#1

No CURSOR, WHILE loop, or User-Defined Function needed.

没有游标、WHILE循环或用户定义的函数。

Just need to be creative with FOR XML and PATH.

只需要对XML和路径有创造性。

[Note: This solution only works on SQL 2005 and later. Original question didn't specify the version in use.]

注意:此解决方案只适用于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

#2

using XML path will not perfectly concatenate as you might expect... it will replace "&" with "&" and will also mess with <" and "> ...maybe a few other things, not sure...but you can try this

使用XML路径将不会像您预期的那样完美地连接在一起。它将用“&”代替“&”，还会打乱“和”>……也许还有其他一些事情，不确定……但是你可以试试这个。

I came across a workaround for this... you need to replace:

我偶然发现了一个变通方法。你需要更换:

FOR XML PATH('')
)

with:

:

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

...or NVARCHAR(MAX) if thats what youre using.

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

why the hell doesn't SQL have a concatenate aggregate function? this is a PITA.

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

#3

I ran into a couple of problems when I tried converting Kevin Fairchild's suggestion to work with strings containing spaces and special XML characters (&, <, >) which were encoded.

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

The final version of my code (which doesn't answer the original question but may be useful to someone) looks like this:

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

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

Rather than using a space as a delimiter and replacing all the spaces with commas, it just pre-pends a comma and space to each value then uses STUFF to remove the first two characters.

它不是使用空格作为分隔符，而是用逗号替换所有空格，它只是预先为每个值预先设置一个逗号和空格，然后使用一些东西来删除前两个字符。

The XML encoding is taken care of automatically by using the TYPE directive.

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

#4

Another option using Sql Server 2005 and above

另一个使用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

#5

If it is SQL Server 2017 or SQL Server Vnext, SQL Azure you can use string_agg as below:

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

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

#6

SQL Server 2005 and later allow you to create your own custom aggregate functions, including for things like concatenation- see the sample at the bottom of the linked article.

SQL Server 2005和稍后允许您创建自己的自定义聚合函数，包括诸如连接之类的东西，请参阅链接文章底部的示例。

#7

Install the SQLCLR Aggregates from http://groupconcat.codeplex.com

从http://groupconcat.codeplex.com安装SQLCLR Aggregates。

Then you can write code like this to get the result you asked for:

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

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;

#8

Eight years later... Microsoft SQL Server vNext Database Engine has finally enhanced Transact-SQL to directly support grouped string concatenation. The Community Technical Preview version 1.0 added the STRING_AGG function and CTP 1.1 added the WITHIN GROUP clause for the STRING_AGG function.

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

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

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

#9

Just to add to what Cade said, this is usually a front-end display thing and should therefore be handled there. I know that sometimes it's easier to write something 100% in SQL for things like file export or other "SQL only" solutions, but most of the times this concatenation should be handled in your display layer.

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

#10

In Oracle you can use LISTAGG aggregate function. An example would be:

在Oracle中，可以使用LISTAGG聚合函数。一个例子是:

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

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

Would result in:

将导致:

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

#11

This kind of question is asked here very often, and the solution is going to depend a lot on the underlying requirements:

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

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

https://*.com/search?q=sql +主

and

和

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

https://*.com/search?q=sql +连接

Typically, there is no SQL-only way to do this without either dynamic sql, a user-defined function, or a cursor.

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

#12

Don't need a cursor... a while loop is sufficient.

不需要鼠标……一个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

#13

This is just an addition to Kevin Fairchild's post (very clever by the way). I would have added it as a comment, but I don't have enough points yet :)

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

I was using this idea for a view I was working on, however the items I was concatinating contained spaces. So I modified the code slightly to not use spaces as delimiters.

我用这个想法来做我正在做的一件事，但是我所收集的东西包含了空间。所以我稍微修改了代码，不使用空格作为分隔符。

Again thanks for the cool workaround 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 

#14

Let's get very simple:

让我们变得非常简单:

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

Replace this line:

取代这一行:

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

With your query.

与您的查询。

#15

You can improve performance significant the following way if group by contains mostly one item:

您可以通过以下方式提高性能，如果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

#16

didn't see any cross apply answers, also no need for xml extraction. Here is a slightly different version of what Kevin Fairchild wrote. It's faster and easier to use in more complex queries:

没有看到任何交叉应用的答案，也不需要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