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

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

使用Stuff和for xml路径操作符将行连接到字符串:Group By两列——>

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

一个例子是

在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

当我尝试将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指令自动处理。

如果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