22,300
社区成员




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'杨开凤'); GO
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'于子涵'); GO
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'穆纨'); GO
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'陈灵竹'); GO
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'郑桂兰'); GO
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'郑启芬'); GO
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'班明露'); GO
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'何鑫琳'); GO
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'王枝'); GO
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'王枝'); GO
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'金青'); GO
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
INSERT INTO [Sheet2]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'31', N'2018-2019', N'蛋白质与酶工程', N'C##学院', N'A', N'杨开凤'); GO
INSERT INTO [Sheet2]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'140', N'2018-2019', N'仪器分析', N'C##学院', N'B/C', N'于子涵/穆纨'); GO
INSERT INTO [Sheet2]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'316', N'2018-2019', N'专业英语', N'C##学院', N'D', N'陈灵竹'); GO
INSERT INTO [Sheet2]([BOOK_ID], [Semester], [course_name], [department_name], [Class_name], [Teacher_name]) VALUES (N'477', N'2018-2019', N'实验C/分析1/实验A1/实验E/实验D', N'C##学院', N'A/B/C/D/E/F/G/H', N'郑桂兰/郑启芬/班明露/何鑫琳/王枝/金青/杨琦'); GO
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;
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