两表的表结构也贴出来吧

USE tempdb

GO

IF OBJECT_ID('[Sheet2222]') IS NOT NULL DROP TABLE [Sheet2222]

GO

CREATE TABLE [Sheet2222](

	[BOOK_ID] INT, 

	[Semester] VARCHAR(20), 

	[course_name] NVARCHAR(30), 

	[department_name] NVARCHAR(20), 

	[Class_name] NVARCHAR(10), 

	[Teacher_name] NVARCHAR(10)

)

GO

SET NOCOUNT ON

INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'31', N'2018-2019', N'蛋白质与酶工程', N'C##学院', N'A', N'杨开凤')

INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'140', N'2018-2019', N'仪器分析', N'C##学院', N'B', N'于子涵')

INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'140', N'2018-2019', N'仪器分析', N'C##学院', N'C', N'穆纨')

INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'316', N'2018-2019', N'专业英语', N'C##学院', N'D', N'陈灵竹')

INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验C', N'C##学院', N'A', N'郑桂兰')

INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验C', N'C##学院', N'B', N'郑启芬')

INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'分析1', N'C##学院', N'C', N'班明露')

INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验A1', N'C##学院', N'D', N'何鑫琳')

INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验A1', N'C##学院', N'E', N'王枝')

INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验E', N'C##学院', N'F', N'王枝')

INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验E', N'C##学院', N'G', N'金青')

INSERT INTO [Sheet2222]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验D', N'C##学院', N'H', N'杨琦')

GO

SELECT BOOK_ID

,[Semester]

,STUFF((SELECT '/'+course_name FROM Sheet2222 AS b WHERE a.BOOK_ID=b.BOOK_ID AND a.Semester=b.Semester FOR XML PATH('')),1,1,'') AS course_name

,a.department_name

,STUFF((SELECT '/'+[Class_name] FROM Sheet2222 AS b WHERE a.BOOK_ID=b.BOOK_ID AND a.Semester=b.Semester FOR XML PATH('')),1,1,'') AS [Class_name]

,STUFF((SELECT '/'+[Teacher_name] FROM Sheet2222 AS b WHERE a.BOOK_ID=b.BOOK_ID AND a.Semester=b.Semester FOR XML PATH('')),1,1,'') AS [Teacher_name]

FROM [Sheet2222] AS a

GROUP BY BOOK_ID,[Semester],a.department_name

SELECT BOOK_ID,

       Semester,

       STUFF(

       (

           SELECT '/' + course_name

           FROM

           (

               SELECT DISTINCT

                   b.course_name

               FROM dbo.Sheet2222 b

               WHERE a.BOOK_ID = b.BOOK_ID

           ) t

           FOR XML PATH('')

       ),

       1,

       1,

       ''

            ) AS course_name,

       a.department_name,

       STUFF(

       (

           SELECT '/' + Class_name

           FROM

           (

               SELECT DISTINCT

                   b.Class_name

               FROM dbo.Sheet2222 b

               WHERE a.BOOK_ID = b.BOOK_ID

           ) t

           FOR XML PATH('')

       ),

       1,

       1,

       ''

            ) AS Class_name,

       STUFF(

       (

           SELECT '/' + Teacher_name

           FROM

           (

               SELECT DISTINCT

                   b.Teacher_name

               FROM dbo.Sheet2222 b

               WHERE a.BOOK_ID = b.BOOK_ID

           ) t

           FOR XML PATH('')

       ),

       1,

       1,

       ''

            ) AS Teacher_name

FROM [Sheet2222] a

GROUP BY BOOK_ID,

         Semester,

         a.department_name;

Quote: 引用 7 楼 二月十六 的回复:

SELECT BOOK_ID,

       Semester,

       STUFF(

       (

           SELECT '/' + course_name

           FROM

           (

               SELECT DISTINCT

                   b.course_name

               FROM dbo.Sheet2222 b

               WHERE a.BOOK_ID = b.BOOK_ID

           ) t

           FOR XML PATH('')

       ),

       1,

       1,

       ''

            ) AS course_name,

       a.department_name,

       STUFF(

       (

           SELECT '/' + Class_name

           FROM

           (

               SELECT DISTINCT

                   b.Class_name

               FROM dbo.Sheet2222 b

               WHERE a.BOOK_ID = b.BOOK_ID

           ) t

           FOR XML PATH('')

       ),

       1,

       1,

       ''

            ) AS Class_name,

       STUFF(

       (

           SELECT '/' + Teacher_name

           FROM

           (

               SELECT DISTINCT

                   b.Teacher_name

               FROM dbo.Sheet2222 b

               WHERE a.BOOK_ID = b.BOOK_ID

           ) t

           FOR XML PATH('')

       ),

       1,

       1,

       ''

            ) AS Teacher_name

FROM [Sheet2222] a

GROUP BY BOOK_ID,

         Semester,

         a.department_name;

版主大神，您这个是符合要求的，但是为什么不用string_agg?
  如果使用string_agg (SELECT DISTINCT
                   b.Class_name
               FROM dbo.Sheet2222 b
               WHERE a.BOOK_ID = b.BOOK_ID,'/')    这个为啥出不来啊。 使用stuff倒是可以出来了。