Pivot function in sql server 2008

Question

how to carry out the pivot function

i have a table with datas

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

but i need the format as

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

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

Tuesday .......

In this way.

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

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

i have used my query

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

and the result comes out as

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    

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

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

Answer 1

try this:

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

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