Pivot misunderstood ?

Hello, i've been looking for some samples on the pivot function, but somewhere i'm making a mistake and can't find where.

This is my statement

SELECT MOV_UI, [1] AS SUNDAY, [2] AS MONDAY, [3] AS THUESDAY, [4] AS WEDNESDAY, [5] AS THURSDAY, AS FRIDAY, [7] AS SATURDAY

FROM XX.UGENT_DAILYSALES_OF_MOVIE_FIRST_WEEK

PIVOT (SUM(TICKETS)

FOR DAYOFWEEK IN ([1], [2], [3], [4], [5], , [7])) AS PVT

ORDER BY MOV_UI

I though to receive a line per mov_ui with the 7 sums, but .. i'm getting a line for each day ....

MOV_UI SUNDAY MONDAY THUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
29 NULL NULL NULL 73 NULL NULL NULL
29 NULL NULL NULL NULL 72 NULL NULL
29 NULL NULL NULL NULL NULL 129 NULL
29 NULL NULL NULL NULL NULL NULL 138
29 104 NULL NULL NULL NULL NULL NULL
29 NULL 68 NULL NULL NULL NULL NULL
29 NULL NULL 58 NULL NULL NULL NULL

What am i missing .... ?

Hmm, this works for me:

CREATE TABLE test
(
movie_ui int,
dayOfWeek int,
tickets int
)
INSERT INTO test
SELECT 29,1,20
UNION ALL
SELECT 29,2,38
UNION ALL
SELECT 30,1,20
UNION ALL
SELECT 30,2,44
GO
SELECT movie_ui, [1] AS SUNDAY, [2] AS MONDAY
FROM test PIVOT (SUM(TICKETS) FOR DAYOFWEEK IN ([1], [2])) AS PVT
ORDER BY movie_ui

movie_ui SUNDAY MONDAY
-- -- --
29 20 38
30 20 44

|||

Thx for the answer, i've found the problem ...

this was my syntax

SELECT MOV_UI, [1] AS SUNDAY, [2] AS MONDAY, [3] AS THUESDAY, [4] AS WEDNESDAY, [5] AS THURSDAY, AS FRIDAY, [7] AS SATURDAY

FROM XX.UGENT_DAILYSALES_OF_MOVIE_FIRST_WEEK

PIVOT (SUM(TICKETS)

FOR DAYOFWEEK IN ([1], [2], [3], [4], [5], , [7])) AS PVT

ORDER BY MOV_UI

Problem was that from the view xx.ugent.. there where other columns returned that where not used in the pivot, but they cause this effect, so adding them in the select mov_ui, ... and the order by helpen the problem, or chaning the 'from xx.ugent' to a

from (select mov_ui, dayofweek, tickets from xx_ugent_dailysales_of_movie_first_Week) p' solved the problem too.

Kind Regards