cross tab query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

cross tab query

Author

Topic

attackmonkey
Starting Member

19 Posts

Posted - 2003-08-17 : 12:31:22

Hi,

I have a table that represents a tv schedule, it has the following columns:

day - int (1-7)
time - foreign key to a list of times in 5 minute increments
week - int (1 c- 58)
description - varchar (name and synopsis of program)

What I want to do is list out a weeks worth of listings in a single select, with the days as the columns and the time id as the row headings, and the propgrammes listed in the correct places in the matrix (each slot only has one program). I'm trying to use a cross tab query, but I can't quite get it to work, can anyone help me with the syntax for this?

I can almost get it to work, but I can't get programmes at the same time on different days to show up on the same day.

Cheers,

Tim.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-17 : 13:30:47

Can you post the query you've already written? And where is it going wrong?

Owais

attackmonkey
Starting Member

19 Posts

Posted - 2003-08-18 : 04:15:56

Here we go:

SELECT times.ti_name,
(CASE programmes.pr_day WHEN 1 THEN programmes.pr_description ELSE '' END) AS [Monday],
(CASE programmes.pr_day WHEN 2 THEN programmes.pr_description ELSE '' END) AS [Tuesday],
(CASE programmes.pr_day WHEN 3 THEN programmes.pr_description ELSE '' END) AS [Wednesday],
(CASE programmes.pr_day WHEN 4 THEN programmes.pr_description ELSE '' END) AS [Thursday],
(CASE programmes.pr_day WHEN 5 THEN programmes.pr_description ELSE '' END) AS [Friday],
(CASE programmes.pr_day WHEN 6 THEN programmes.pr_description ELSE '' END) AS [Saturday],
(CASE programmes.pr_day WHEN 7 THEN programmes.pr_description ELSE '' END) AS [Sunday]
FROM times LEFT OUTER JOIN programmes ON times.ti_id = programmes.pr_time

which gives me the required matrix, but programmes on the same time on different days are on different rows rather than all being on the same row. I can get it to work using aggregates and groups with numeric data, but not with text data. Any ideas?

Tim.

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-18 : 04:53:21

I think you will need to use aggregated groups. Does MAX (case ...) not work?

-------
Moo. :)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-18 : 08:51:47

Mr. Mist hit the right spot...try this:


SELECT ti_name, MAX(Monday), MAX(Tuesday), MAX(Wednesday), MAX(Thursday), MAX(Friday), MAX(Saturday), MAX(Sunday)
FROM(
SELECT times.ti_name, 
(CASE programmes.pr_day WHEN 1 THEN programmes.pr_description ELSE '' END) AS [Monday], 
(CASE programmes.pr_day WHEN 2 THEN programmes.pr_description ELSE '' END) AS [Tuesday], 
(CASE programmes.pr_day WHEN 3 THEN programmes.pr_description ELSE '' END) AS [Wednesday], 
(CASE programmes.pr_day WHEN 4 THEN programmes.pr_description ELSE '' END) AS [Thursday], 
(CASE programmes.pr_day WHEN 5 THEN programmes.pr_description ELSE '' END) AS [Friday], 
(CASE programmes.pr_day WHEN 6 THEN programmes.pr_description ELSE '' END) AS [Saturday], 
(CASE programmes.pr_day WHEN 7 THEN programmes.pr_description ELSE '' END) AS [Sunday]
FROM times LEFT OUTER JOIN programmes ON times.ti_id = programmes.pr_time
) A
GROUP BY ti_name


SELECT times.ti_name, 
MAX(CASE programmes.pr_day WHEN 1 THEN programmes.pr_description ELSE '' END) AS [Monday], 
MAX(CASE programmes.pr_day WHEN 2 THEN programmes.pr_description ELSE '' END) AS [Tuesday], 
MAX(CASE programmes.pr_day WHEN 3 THEN programmes.pr_description ELSE '' END) AS [Wednesday], 
MAX(CASE programmes.pr_day WHEN 4 THEN programmes.pr_description ELSE '' END) AS [Thursday], 
MAX(CASE programmes.pr_day WHEN 5 THEN programmes.pr_description ELSE '' END) AS [Friday], 
MAX(CASE programmes.pr_day WHEN 6 THEN programmes.pr_description ELSE '' END) AS [Saturday], 
MAX(CASE programmes.pr_day WHEN 7 THEN programmes.pr_description ELSE '' END) AS [Sunday]
FROM times LEFT OUTER JOIN programmes ON times.ti_id = programmes.pr_time
GROUP BY ti_name

Owais

attackmonkey
Starting Member

19 Posts

Posted - 2003-08-18 : 11:34:07

That did indeed do the trick, thank you all for the help!

Tim.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources