在Chris Shaffer的回答之上：

如果您的数据可能会重复，例如

Tom
Ali
John
Ali
Tom
Mike

而不是汤姆、阿里、约翰、阿里、汤姆、迈克

您可以使用DISTINCT避免重复，并让Tom、Ali、John、Mike：

DECLARE @Names VARCHAR(8000)
SELECT DISTINCT @Names = COALESCE(@Names + ',', '') + Name
FROM People
WHERE Name IS NOT NULL
SELECT @Names

在Oracle中，它是wm_concat。我相信这个功能在10g及更高版本中可用。

如果要处理null，可以通过添加where子句或在第一个子句周围添加另一个COALENCE来完成。

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(COALESCE(@Names + ', ', '') + Name, @Names) FROM People

下面是一个使用“基本循环”和“rownum”实现给定场景的简单PL/SQL过程

表定义

CREATE TABLE "NAMES" ("NAME" VARCHAR2(10 BYTE))) ;

让我们将值插入此表

INSERT INTO NAMES VALUES('PETER');
INSERT INTO NAMES VALUES('PAUL');
INSERT INTO NAMES VALUES('MARY');

程序从这里开始

DECLARE 

MAXNUM INTEGER;
CNTR INTEGER := 1;
C_NAME NAMES.NAME%TYPE;
NSTR VARCHAR2(50);

BEGIN

SELECT MAX(ROWNUM) INTO MAXNUM FROM NAMES;

LOOP

SELECT NAME INTO  C_NAME FROM 
(SELECT ROWNUM RW, NAME FROM NAMES ) P WHERE P.RW = CNTR;

NSTR := NSTR ||','||C_NAME;
CNTR := CNTR + 1;
EXIT WHEN CNTR > MAXNUM;

END LOOP;

dbms_output.put_line(SUBSTR(NSTR,2));

END;

后果

PETER,PAUL,MARY

以下是实现这一目标的完整解决方案：

-- Table Creation
CREATE TABLE Tbl
( CustomerCode    VARCHAR(50)
, CustomerName    VARCHAR(50)
, Type VARCHAR(50)
,Items    VARCHAR(50)
)

insert into Tbl
SELECT 'C0001','Thomas','BREAKFAST','Milk'
union SELECT 'C0001','Thomas','BREAKFAST','Bread'
union SELECT 'C0001','Thomas','BREAKFAST','Egg'
union SELECT 'C0001','Thomas','LUNCH','Rice'
union SELECT 'C0001','Thomas','LUNCH','Fish Curry'
union SELECT 'C0001','Thomas','LUNCH','Lessy'
union SELECT 'C0002','JOSEPH','BREAKFAST','Bread'
union SELECT 'C0002','JOSEPH','BREAKFAST','Jam'
union SELECT 'C0002','JOSEPH','BREAKFAST','Tea'
union SELECT 'C0002','JOSEPH','Supper','Tea'
union SELECT 'C0002','JOSEPH','Brunch','Roti'

-- function creation
GO
CREATE  FUNCTION [dbo].[fn_GetItemsByType]
(   
    @CustomerCode VARCHAR(50)
    ,@Type VARCHAR(50)
)
RETURNS @ItemType TABLE  ( Items VARCHAR(5000) )
AS
BEGIN

        INSERT INTO @ItemType(Items)
    SELECT  STUFF((SELECT distinct ',' + [Items]
         FROM Tbl 
         WHERE CustomerCode = @CustomerCode
            AND Type=@Type
            FOR XML PATH(''))
        ,1,1,'') as  Items



    RETURN 
END

GO

-- fianl Query
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Type) 
                    from Tbl
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT CustomerCode,CustomerName,' + @cols + '
             from 
             (
                select  
                    distinct CustomerCode
                    ,CustomerName
                    ,Type
                    ,F.Items
                    FROM Tbl T
                    CROSS APPLY [fn_GetItemsByType] (T.CustomerCode,T.Type) F
            ) x
            pivot 
            (
                max(Items)
                for Type in (' + @cols + ')
            ) p '

execute(@query) 

我们可以使用RECUSRSIVITY、WITH CTE、union ALL，如下所示

declare @mytable as table(id int identity(1,1), str nvarchar(100))
insert into @mytable values('Peter'),('Paul'),('Mary')

declare @myresult as table(id int,str nvarchar(max),ind int, R# int)

;with cte as(select id,cast(str as nvarchar(100)) as str, cast(0 as int) ind from @mytable
union all
select t2.id,cast(t1.str+',' +t2.str as nvarchar(100)) ,t1.ind+1 from cte t1 inner join @mytable t2 on t2.id=t1.id+1)
insert into @myresult select *,row_number() over(order by ind) R# from cte

select top 1 str from @myresult order by R# desc