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

-- 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) 

对于其他答案，阅读答案的人必须知道特定的域表，例如车辆或学生。必须创建该表并用数据填充该表以测试解决方案。

下面是一个使用SQL Server“Information_Schema.Columns”表的示例。通过使用此解决方案，不需要创建表或添加数据。此示例为数据库中的所有表创建一个逗号分隔的列名列表。

SELECT
    Table_Name
    ,STUFF((
        SELECT ',' + Column_Name
        FROM INFORMATION_SCHEMA.Columns Columns
        WHERE Tables.Table_Name = Columns.Table_Name
        ORDER BY Column_Name
        FOR XML PATH ('')), 1, 1, ''
    )Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME 

SQL Server 2005或更高版本

CREATE TABLE dbo.Students
(
    StudentId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Students PRIMARY KEY (StudentId)
);

CREATE TABLE dbo.Subjects
(
    SubjectId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Subjects PRIMARY KEY (SubjectId)
);

CREATE TABLE dbo.Schedules
(
    StudentId INT
    , SubjectId INT
    , CONSTRAINT PK__Schedule PRIMARY KEY (StudentId, SubjectId)
    , CONSTRAINT FK_Schedule_Students FOREIGN KEY (StudentId) REFERENCES dbo.Students (StudentId)
    , CONSTRAINT FK_Schedule_Subjects FOREIGN KEY (SubjectId) REFERENCES dbo.Subjects (SubjectId)
);

INSERT dbo.Students (StudentId, Name) VALUES
    (1, 'Mary')
    , (2, 'John')
    , (3, 'Sam')
    , (4, 'Alaina')
    , (5, 'Edward')
;

INSERT dbo.Subjects (SubjectId, Name) VALUES
    (1, 'Physics')
    , (2, 'Geography')
    , (3, 'French')
    , (4, 'Gymnastics')
;

INSERT dbo.Schedules (StudentId, SubjectId) VALUES
    (1, 1)        --Mary, Physics
    , (2, 1)    --John, Physics
    , (3, 1)    --Sam, Physics
    , (4, 2)    --Alaina, Geography
    , (5, 2)    --Edward, Geography
;

SELECT
    sub.SubjectId
    , sub.Name AS [SubjectName]
    , ISNULL( x.Students, '') AS Students
FROM
    dbo.Subjects sub
    OUTER APPLY
    (
        SELECT
            CASE ROW_NUMBER() OVER (ORDER BY stu.Name) WHEN 1 THEN '' ELSE ', ' END
            + stu.Name
        FROM
            dbo.Students stu
            INNER JOIN dbo.Schedules sch
                ON stu.StudentId = sch.StudentId
        WHERE
            sch.SubjectId = sub.SubjectId
        ORDER BY
            stu.Name
        FOR XML PATH('')
    ) x (Students)
;

一个现成的解决方案，没有额外的逗号：

select substring(
        (select ', '+Name AS 'data()' from Names for xml path(''))
       ,3, 255) as "MyList"

空列表将导致NULL值。通常，您会将列表插入到表列或程序变量中：根据需要调整最大长度255。

（迪瓦卡尔和延斯·弗兰森提供了很好的答案，但需要改进。）

在SQL Server中实现这一点的一种方法是将表内容返回为XML（对于XML原始），将结果转换为字符串，然后用“，”替换标记。

此方法仅适用于Teradata Aster数据库，因为它使用NPATH函数。

再次，我们有桌上学生

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

然后使用NPATH，只需一次SELECT：

SELECT * FROM npath(
  ON Students
  PARTITION BY SubjectID
  ORDER BY StudentName
  MODE(nonoverlapping)
  PATTERN('A*')
  SYMBOLS(
    'true' as A
  )
  RESULT(
    FIRST(SubjectID of A) as SubjectID,
    ACCUMULATE(StudentName of A) as StudentName
  )
);

结果：

SubjectID       StudentName
----------      -------------
1               [John, Mary, Sam]
2               [Alaina, Edward]