query that turns column values into column headers

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)

query that turns column values into column headers

Author

Topic

hulte
Starting Member

1 Post

Posted - 2011-10-21 : 17:03:15

Hi,

I am stuck with a nice question.
It is quite hard to explain it in words so here is an example:

-- Create some tables

CREATE TABLE WeekDay
(
WeekDayId int,
WeekDay nvarchar(50)
)

CREATE TABLE Person
(
PersonId int,
Person nvarchar(50)
)

CREATE TABLE Appointment
(
WeekDayId int,
PersonId int,
)

-- Populate the created tables

INSERT INTO WeekDay (WeekDayId, WeekDay) VALUES ( 1, 'Mon');
INSERT INTO WeekDay (WeekDayId, WeekDay) VALUES ( 2, 'Tue');
INSERT INTO WeekDay (WeekDayId, WeekDay) VALUES ( 3, 'Wed');
INSERT INTO WeekDay (WeekDayId, WeekDay) VALUES ( 4, 'Thu');
INSERT INTO WeekDay (WeekDayId, WeekDay) VALUES ( 5, 'Fri');

INSERT INTO Person (PersonId, Person) VALUES ( 10, 'John');
INSERT INTO Person (PersonId, Person) VALUES ( 20, 'Mary');
INSERT INTO Person (PersonId, Person) VALUES ( 30, 'Steve');

INSERT INTO Appointment (WeekDayId, PersonId) VALUES ( 1, 10);
INSERT INTO Appointment (WeekDayId, PersonId) VALUES ( 2, 10);
INSERT INTO Appointment (WeekDayId, PersonId) VALUES ( 3, 10);
INSERT INTO Appointment (WeekDayId, PersonId) VALUES ( 3, 20);
INSERT INTO Appointment (WeekDayId, PersonId) VALUES ( 5, 20);
INSERT INTO Appointment (WeekDayId, PersonId) VALUES ( 3, 30);

-- a query and the query result output

SELECT P.Person, W.WeekDay
FROM Person P
JOIN Appointment A
ON P.PersonId = A.PersonId
RIGHT JOIN Weekday W
ON W.WeekDayId = A.WeekDAyId

Person WeekDay
---------------
John Mon
John Tue
John Wed
Mary Wed
Steve Wed
NULL Thu
Mary Fri

-- Now here comes the actual question
-- I want a query that give me the following result
-- Maybe it can not be done in sql and tsql is a better option...)

Person Mon Tue Wed Thu Fry
---------------------------
John V V V X X
Mary X X V X X
Steve X X V X V

Hope one of you can help me out!!
Thanks in advance...

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-10-21 : 19:55:27

[code]select Person,
sum(case when a.WeekDayId = 1 then 1 else 0 end) as [Mon],
sum(case when a.WeekDayId = 2 then 1 else 0 end) as [Tue],
sum(case when a.WeekDayId = 3 then 1 else 0 end) as [Wed],
sum(case when a.WeekDayId = 4 then 1 else 0 end) as [Thu],
sum(case when a.WeekDayId = 5 then 1 else 0 end) as [Fri]
from Person p
join Appointment a on
p.PersonId = a.PersonId
group
by Person;[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-22 : 00:40:01

[code]
select *
from (
select p.Person, w.[WeekDay], flag = case when a.WeekDayId is null then 'X' else 'V' end
from Person p
cross join [WeekDay] w
left join Appointment a on p.PersonId = a.PersonId
and w.WeekDayId = a.WeekDayId

) d
pivot
(
max([flag])
for [WeekDay] in ([Mon], [Tue], [Wed], [Thu], [Fri])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources