SQL - making rows columns - 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 2005 Forums

Transact-SQL (2005)

SQL - making rows columns

Author

Topic

niico
Starting Member

1 Post

Posted - 2010-09-05 : 13:28:34

I have a table containing number of times an event occurs on a day of the week, for night or day (n or d), DayNmbers are 0-6 (sun-sat). Truncated example:

ID dayNumber eventID quantity nightDay
1 1 6 0 d
1 1 6 0 n
1 2 6 0 d
1 2 6 2 n
1 1 7 2 d
1 1 7 0 n
1 1 8 5 d
1 1 8 0 n

definition
eventIDs
6: Gig
7: Cinema
8: Football

I would like to display it in a grid with first event name, then days as columns (n then d):

Event Type m(d) | m(n) | t(d) | t(n) | w(d)....
Cinama 2 0 0 1 3
Gig 0 2 0 2 0
Football 5 0 0 1

Any help greatly appreciated!

singularity
Posting Yak Master

153 Posts

Posted - 2010-09-05 : 13:37:27

[code]
select case eventID when 6 then 'Gig'
when 7 then 'Cinema'
when 8 then 'Football' end as EventType,
sum(case when dayNumber = 1 and nightDay = 'd' then quantity else 0 end) as MondayDay,
sum(case when dayNumber = 1 and nightDay = 'n' then quantity else 0 end) as MondayNight,
sum(case when dayNumber = 2 and nightDay = 'd' then quantity else 0 end) as TuesdayDay,
sum(case when dayNumber = 2 and nightDay = 'n' then quantity else 0 end) as TuesdayNight,
sum(case when dayNumber = 3 and nightDay = 'd' then quantity else 0 end) as WednesdayDay,
sum(case when dayNumber = 3 and nightDay = 'n' then quantity else 0 end) as WednesdayNight,
sum(case when dayNumber = 4 and nightDay = 'd' then quantity else 0 end) as ThursdayDay,
sum(case when dayNumber = 4 and nightDay = 'n' then quantity else 0 end) as ThursdayNight,
sum(case when dayNumber = 5 and nightDay = 'd' then quantity else 0 end) as FridayDay,
sum(case when dayNumber = 5 and nightDay = 'n' then quantity else 0 end) as FridayNight,
sum(case when dayNumber = 6 and nightDay = 'd' then quantity else 0 end) as SaturdayDay,
sum(case when dayNumber = 6 and nightDay = 'n' then quantity else 0 end) as SaturdayNight,
sum(case when dayNumber = 0 and nightDay = 'd' then quantity else 0 end) as SundayDay,
sum(case when dayNumber = 0 and nightDay = 'n' then quantity else 0 end) as SundayNight
from yourtable
group by case eventID when 6 then 'Gig'
when 7 then 'Cinema'
when 8 then 'Football' end
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources