SQL Server 2008中的枢轴功能 [英] Pivot function in sql server 2008

问题描述

如何执行支点功能

我有一个包含数据的表

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

以这种方式.

如何执行枢轴功能以获取这种格式.

How to Perform the pivot function to get in this format.

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

Please help me out........

我用了我的查询

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个星期一的记录，而不是一个星期一的记录...

but here too i have 3 monday records and not as one single monday record...

如何获得每天1天的一项记录... ??

How to get One Record for 1 day each...??

推荐答案

尝试一下:

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运算符未消耗"的任何列将保留在最终结果集中，从而增加了基数.

Any column not "consumed" by the PIVOT operator will remain in the final result set, increasing its cardinality.

这篇关于SQL Server 2008中的枢轴功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！