Cross Tab PIVOT / CASE ?
-
Hi All,
I have some timetable info for rooms in a school as follows:
Description period room
======================
Maths 1 R10
English 2 R12
There are 9 periods each day and 85+ rooms and lots of lessons. I need the data like this:
Room Period 1 Period 2 Period 3
=================================
R10 Maths Englsh (etc)
I've tried the following case statement
SELECT Room,
CASE Period WHEN 1 THEN Description ELSE NULL END AS 'Period 1',
CASE Period WHEN 2 THEN Description ELSE NULL END AS 'Period 2',
CASE Period WHEN 3 THEN Description ELSE NULL END AS 'Period 3',
CASE Period WHEN 4 THEN Description ELSE NULL END AS 'Period 4',
CASE Period WHEN 5 THEN Description ELSE NULL END AS 'Period 5',
CASE Period WHEN 6 THEN Description ELSE NULL END AS 'Period 6',
CASE Period WHEN 7 THEN Description ELSE NULL END AS 'Period 7',
CASE Period WHEN 8 THEN Description ELSE NULL END AS 'Period 8',
CASE Period WHEN 9 THEN Description ELSE NULL END AS 'Period 9'
FROM timetabledata220506
The the results are as follows
Room Period 1 Period 2 Period 3 Period 4 Period 5 Period 6 (etc)
===================================================
R10 Maths NULL NULL NULL NULL NULL
R10 NULL Maths NULL NULL NULL NULL
R10 NULL NULL English NULL NULL NULL
(Etc)
How can i get this all on one line ? there will be NULL Periods where the room is empty.
Any help would be great
Cheers
DAve
-
Use group by and some summary function. e.g.
SELECT Room,
max(CASE Period WHEN 1 THEN Description ELSE NULL END) AS 'Period 1',
max(CASE Period WHEN 2 THEN Description ELSE NULL END) AS 'Period 2',
max(CASE Period WHEN 3 THEN Description ELSE NULL END) AS 'Period 3',
max(CASE Period WHEN 4 THEN Description ELSE NULL END) AS 'Period 4',
max(CASE Period WHEN 5 THEN Description ELSE NULL END) AS 'Period 5',
max(CASE Period WHEN 6 THEN Description ELSE NULL END) AS 'Period 6',
max(CASE Period WHEN 7 THEN Description ELSE NULL END) AS 'Period 7',
max(CASE Period WHEN 8 THEN Description ELSE NULL END) AS 'Period 8',
max(CASE Period WHEN 9 THEN Description ELSE NULL END) AS 'Period 9'
FROM timetabledata220506
group by Room
Also, see these...
http://www.sqlteam.com/item.asp?ItemID=2955
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Thanks mate, this works great !!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply