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

 
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
 

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

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

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

SELECT id, STRING_AGG(CONCAT(name, ':', [value]), ', ')
FROM #YourTable 
GROUP BY id

一个例子是

在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

SQL Server 2005及其后续版本允许您创建自己的自定义聚合函数，包括像连接这样的功能—请参阅链接文章底部的示例。