此为View_temp:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----TNT 6.29 修改
----功能: 用于存储过程cp_SelArrrangeResultByTermIDFloorID ,视图为所有楼房的所有教室开课信息
ALTER View view_temp
as SELECT 课程表 = a.CourseDesc, b.CourseName,
(SELECT DISTINCT
CASE b.RoomName WHEN roomName THEN roomName END) AS roomName,
floorID, TermID
FROM (SELECT 1 AS arrangeOrder, '周一1.2节' AS CourseDesc, 1 AS type
UNION
SELECT 1 AS arrangeOrder, '周一3.4节' AS CourseDesc, 2 AS type
UNION
SELECT 1 AS arrangeOrder, '周一5.6节' AS CourseDesc, 3 AS type
UNION
SELECT 1 AS arrangeOrder, '周一7.8节' AS CourseDesc, 4 AS type
UNION
SELECT 1 AS arrangeOrder, NULL AS CourseDesc, 5 AS type
UNION
SELECT 2 AS arrangeOrder, '周二1.2节' AS CourseDesc, 1 AS type
UNION
SELECT 2 AS arrangeOrder, '周二3.4节' AS CourseDesc, 2 AS type
UNION
SELECT 2 AS arrangeOrder, '周二5.6节' AS CourseDesc, 3 AS type
UNION
SELECT 2 AS arrangeOrder, '周二7.8节' AS CourseDesc, 4 AS type
UNION
SELECT 2 AS arrangeOrder, NULL AS CourseDesc, 5 AS type
UNION
SELECT 3 AS arrangeOrder, '周三1.2节' AS CourseDesc, 1 AS type
UNION
SELECT 3 AS arrangeOrder, '周三3.4节' AS CourseDesc, 2 AS type
UNION
SELECT 3 AS arrangeOrder, '周三5.6节' AS CourseDesc, 3 AS type
UNION
SELECT 3 AS arrangeOrder, '周三7.8节' AS CourseDesc, 4 AS type
UNION
SELECT 3 AS arrangeOrder, NULL AS CourseDesc, 5 AS type
UNION
SELECT 4 AS arrangeOrder, '周四1.2节' AS CourseDesc, 1 AS type
UNION
SELECT 4 AS arrangeOrder, '周四3.4节' AS CourseDesc, 2 AS type
UNION
SELECT 4 AS arrangeOrder, '周四5.6节' AS CourseDesc, 3 AS type
UNION
SELECT 4 AS arrangeOrder, '周四7.8节' AS CourseDesc, 4 AS type
UNION
SELECT 4 AS arrangeOrder, NULL AS CourseDesc, 5 AS type
UNION
SELECT 5 AS arrangeOrder, '周五1.2节' AS CourseDesc, 1 AS type
UNION
SELECT 5 AS arrangeOrder, '周五3.4节' AS CourseDesc, 2 AS type
UNION
SELECT 5 AS arrangeOrder, '周五5.6节' AS CourseDesc, 3 AS type
UNION
SELECT 5 AS arrangeOrder, '周五7.8节' AS CourseDesc, 4 AS type
UNION
SELECT 5 AS arrangeOrder, NULL AS CourseDesc, 5 AS type) a inner JOIN
(SELECT FloorID, TermID, CourseName, roomName, arrangeOrder, type = 1
FROM V_ArrangeResult) b ON a.arrangeOrder = b.arrangeOrder AND a.type = b.type
group by a.CourseDesc,b.CourseName,b.RoomName,b.termID,b.floorIDGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----TNT 6.29 修改
----功能: 用于存储过程cp_SelArrrangeResultByTermIDFloorID ,视图为所有楼房的所有教室开课信息
ALTER View view_temp
as SELECT 课程表 = a.CourseDesc, b.CourseName,
(SELECT DISTINCT
CASE b.RoomName WHEN roomName THEN roomName END) AS roomName,
floorID, TermID
FROM (SELECT 1 AS arrangeOrder, '周一1.2节' AS CourseDesc, 1 AS type
UNION
SELECT 1 AS arrangeOrder, '周一3.4节' AS CourseDesc, 2 AS type
UNION
SELECT 1 AS arrangeOrder, '周一5.6节' AS CourseDesc, 3 AS type
UNION
SELECT 1 AS arrangeOrder, '周一7.8节' AS CourseDesc, 4 AS type
UNION
SELECT 1 AS arrangeOrder, NULL AS CourseDesc, 5 AS type
UNION
SELECT 2 AS arrangeOrder, '周二1.2节' AS CourseDesc, 1 AS type
UNION
SELECT 2 AS arrangeOrder, '周二3.4节' AS CourseDesc, 2 AS type
UNION
SELECT 2 AS arrangeOrder, '周二5.6节' AS CourseDesc, 3 AS type
UNION
SELECT 2 AS arrangeOrder, '周二7.8节' AS CourseDesc, 4 AS type
UNION
SELECT 2 AS arrangeOrder, NULL AS CourseDesc, 5 AS type
UNION
SELECT 3 AS arrangeOrder, '周三1.2节' AS CourseDesc, 1 AS type
UNION
SELECT 3 AS arrangeOrder, '周三3.4节' AS CourseDesc, 2 AS type
UNION
SELECT 3 AS arrangeOrder, '周三5.6节' AS CourseDesc, 3 AS type
UNION
SELECT 3 AS arrangeOrder, '周三7.8节' AS CourseDesc, 4 AS type
UNION
SELECT 3 AS arrangeOrder, NULL AS CourseDesc, 5 AS type
UNION
SELECT 4 AS arrangeOrder, '周四1.2节' AS CourseDesc, 1 AS type
UNION
SELECT 4 AS arrangeOrder, '周四3.4节' AS CourseDesc, 2 AS type
UNION
SELECT 4 AS arrangeOrder, '周四5.6节' AS CourseDesc, 3 AS type
UNION
SELECT 4 AS arrangeOrder, '周四7.8节' AS CourseDesc, 4 AS type
UNION
SELECT 4 AS arrangeOrder, NULL AS CourseDesc, 5 AS type
UNION
SELECT 5 AS arrangeOrder, '周五1.2节' AS CourseDesc, 1 AS type
UNION
SELECT 5 AS arrangeOrder, '周五3.4节' AS CourseDesc, 2 AS type
UNION
SELECT 5 AS arrangeOrder, '周五5.6节' AS CourseDesc, 3 AS type
UNION
SELECT 5 AS arrangeOrder, '周五7.8节' AS CourseDesc, 4 AS type
UNION
SELECT 5 AS arrangeOrder, NULL AS CourseDesc, 5 AS type) a inner JOIN
(SELECT FloorID, TermID, CourseName, roomName, arrangeOrder, type = 1
FROM V_ArrangeResult) b ON a.arrangeOrder = b.arrangeOrder AND a.type = b.type
group by a.CourseDesc,b.CourseName,b.RoomName,b.termID,b.floorIDGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
set @s=''
select @s=@s+','+quotename(rtrim(RoomNamme))
+'=max(case when RoomNamme='+quotename(rtrim(RoomNamme),'''')
+' then CourseName else '''' end)'
from(select distinct RoomNamme from View_temp)a
exec('select 课程表'+@s+' from View_temp group by 课程表')
set @s = ''
select @s = @s + ','+RoomNamme+'=max(case RoomNamme when '''+RoomNamme+''' then CourseName end)'
set @s = 'select 课程表'+@s+' from View_temp where FloorID = 16 group by 课程表'
exec(@s)
set @s = ''select @s = @s + ','+RoomNamme+'=max(case RoomNamme when '''+RoomNamme+''' then CourseName end)'
from View_temp where FloorID = 16 group by RoomNammeset @s = 'select 课程表'+@s+' from View_temp where FloorID = 16'exec(@s)
课程表 RoomName RoomName周一12节 CourseName CourseName .....
周一34节
周一56节
.......
请问如何再做修改?
谢谢
set @s = ''select @s = @s + ','+RoomNamme+'=max(case b.RoomNamme when '''+RoomNamme+''' then b.CourseName end)'
from View_temp where FloorID = 16 group by RoomNammeset @s = 'select a.课程表'+@s+' from (select distinct CourseDesc from View_temp) a '
+' left join View_temp b on a.课程表=b.课程表 '
+' where b.FloorID = 16 group by a.课程表'exec(@s)