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

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

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

and

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

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

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

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

我想对所有的医护人员说:

 
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