Creating Crosstab with CASE 1

[cwsstins]

I'm trying to produce a crosstab to move rows into columns using CASE statements. Trying to stay away from PIVOT because there are multiple columns I want to move and it looks like PIVOT will be too complex.

Tables: Person, Schedule

Schedule table has multiple records for one Person. I've got joins to other tables as well. What I'm looking to do is crosstab the rows into columns for the Schedule table. So instead of:

George sched1
George sched2
Geroge sched3

I want:
George sched1 sched2 sched3

I'm using this:

,MAX(CASE Schedule.Sched WHEN '1' THEN 
(CASE WHEN Schedule.Status = 'R' THEN 'Ready' WHEN Schedule.Status = 'C' THEN 'Canceled' WHEN Schedule.Status = 'P' THEN 'Paid' WHEN Schedule.Status = 'S' THEN 'Scheduled' END) END) as Sched1_SchedStatus
,MAX(CASE Schedule.Sched WHEN '2' THEN 
(CASE WHEN Schedule.Status = 'R' THEN 'Ready' WHEN Schedule.Status = 'C' THEN 'Canceled' WHEN Schedule.Status = 'P' THEN 'Paid' WHEN Schedule.Status = 'S' THEN 'Scheduled' END) END) as Sched2_SchedStatus
,MAX(CASE Schedule.Sched WHEN '3' THEN 
(CASE WHEN Schedule.Status = 'R' THEN 'Ready' WHEN Schedule.Status = 'C' THEN 'Canceled' WHEN Schedule.Status = 'P' THEN 'Paid' WHEN Schedule.Status = 'S' THEN 'Scheduled' END) END) as Sched3_SchedStatus
,MAX(CASE Schedule.Sched WHEN '4' THEN 
(CASE WHEN Schedule.Status = 'R' THEN 'Ready' WHEN Schedule.Status = 'C' THEN 'Canceled' WHEN Schedule.Status = 'P' THEN 'Paid' WHEN Schedule.Status = 'S' THEN 'Scheduled' END) END) as Sched4_SchedStatus

and this is what I'm getting. Each record is still being evaluated and displayed with a NULL where it doesn't match the CASE:

Person Schd1 Schd2 Schd3
George Paid NULL NULL
George NULL Paid NULL
George NULL NULL Paid

 

[gmmastros]

What does your Group By clause look like. If I had to guess, I would say you have too many columns. If I had to guess, I would say that the only column you need in the group by is Person.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cwsstins]

I have about 12 fields in my GROUP BY because if I don't add them there, I get the "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" message since they are also in my SELECT. Basically, there are about 12 fields being selected as is and another 15 or so that I'm using the MAX(CASE...) statement on to move to columns.

 

[gmmastros]

Are you returning Schedule.Sched or Schedule.Status in the select list. Can you take these 2 columns out of the group by?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cwsstins]

Geez...yes. I feel stupid. Thanks much, that was the problem. Looks good now : )