SQL Server 2008中的枢轴功能

Question

如何执行枢纽功能

我有一张有数据的表

Day     Period  subject  fromtime  totime

 ---------------------------------------------- 
Monday  1st   English   9:30    10:15

Monday  1st   English   9:30    10:15

Monday  5th   English   1:30    2:20

Monday  8th   English   3:40    4:30

但我需要的格式为

day     period(1st)     2nd    3rd...... 5th...          8th

--------------------------------------------------------------------------
Monday    1st           nill    nill     5th             8th
        english                        english        english

Tuesday .......

这样。

如何执行数据透视功能以这种格式获取。

请帮帮我........

我用过我的查询

SELECT DAY, [1st],[2nd],[3rd],[4th], [5th],[6th],[7th], [8th]
FROM 
    (
        SELECT Day, Period, Subject,FromTime,ToTime
        FROM StaffTimeTableEntry
    ) up
PIVOT (Min(Subject) FOR Period IN ([1st],[2nd],[3rd],[4th], [5th],[6th],[7th], [8th])) AS pvt
order by Day

结果显示为

day        1st     2nd      3rd     4th    5th     6th     7th    8th

----------------------------------------------------------------------------------
Friday  NULL    NULL    NULL    NULL    NULL    NULL    English NULL

Monday  NULL    NULL    NULL    NULL    English NULL    NULL    NULL

Monday  NULL    NULL    NULL    NULL    NULL    NULL    NULL    English

Monday  English NULL    NULL    NULL    NULL    NULL    NULL    NULL

Saturday    NULL    NULL    NULL    NULL    NULL    English NULL

Thursday    NULL    NULL    NULL    English NULL    NULL    NULL

Tuesday NULL    English NULL    NULL    NULL    NULL    NULL    NULL

Tuesday NULL    NULL    NULL    NULL    NULL    English NULL    NULL

Wednesday   NULL    NULL    NULL    NULL    NULL    English NULL

但在这里我也有3个星期一的记录，而不是一个星期一的记录…

如何获得每天1天的一个记录…？

Answer 1

试试这个：

SELECT DAY, [1st],[2nd],[3rd],[4th], [5th],[6th],[7th], [8th]
FROM 
    (
        SELECT Day, Period, Subject --,FromTime,ToTime
        FROM StaffTimeTableEntry
    ) up
PIVOT (Min(Subject) FOR Period IN ([1st],[2nd],[3rd],[4th], [5th],[6th],[7th], [8th])) AS pvt
order by Day

PIVOT运算符未“消耗”的任何列将保留在最终结果集中，从而增加了基数。