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

不需要光标…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

这只是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 

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

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

and

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

通常，如果没有动态sql、用户定义函数或游标，就没有仅使用sql的方法来完成此任务。

使用XML路径不会完美地连接你可能期望…它会将“&”替换为“&”，也会混淆<”和“>” .．.也许还有其他一些事情，不确定……但是你可以试试这个

我想到了一个变通办法…您需要替换:

FOR XML PATH('')
)

:

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

.．.或者NVARCHAR(MAX)如果这是你使用的。

为什么SQL没有一个串联聚合函数?这是PITA。

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