Quote:
quote:Originally posted by phix
This gets me close, but it lists all 24 different region/Tier
combinations.
select [PType],
(case when ([region] = 'R1' and [tier] = '1') then [NewRate] end) As R1,
(case when ([region] = 'R2' and [tier] = '1') then [NewRate] end) As R2,
(case when ([region] = 'R3' and [tier] = '1') then [NewRate] end) As R3,
(case when ([region] = 'R4' and [tier] = '1') then [NewRate] end) As R4,
(case when ([region] = 'R5' and [tier] = '1') then [NewRate] end) As R5,
(case when ([region] = 'R6' and [tier] = '1') then [NewRate] end) As R6
from tblFY06FY07Compare
group by [region], [PType], [Tier], [NewRate]
order by [PType]
I get this...
RT01 45.90 NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL 47.20 NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL 52.50 NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL 57.05 NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL 45.90 NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL 52.20
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT01 NULL NULL NULL NULL NULL NULL
RT02 43.70 NULL NULL NULL NULL NULL
RT02 NULL NULL NULL NULL NULL NULL
RT02 NULL NULL NULL NULL NULL NULL
|
Hi!
Did you manage to sort this problem? Well its quite sometime i know but i hope i would share this experience with you as i landed into the same task and came up with an interesting solution. Just add a MAX in your CASE and null will be eliminated by aggregate!!!!
Your new select should look like this now
select [PType],
MAX(case when ([region] = 'R1' and [tier] = '1') then [NewRate] end) As R1,
MAX(case when ([region] = 'R2' and [tier] = '1') then [NewRate] end) As R2,
MAX(case when ([region] = 'R3' and [tier] = '1') then [NewRate] end) As R3,
MAX(case when ([region] = 'R4' and [tier] = '1') then [NewRate] end) As R4,
MAX(case when ([region] = 'R5' and [tier] = '1') then [NewRate] end) As R5,
MAX(case when ([region] = 'R6' and [tier] = '1') then [NewRate] end) As R6
from tblFY06FY07Compare
group by [region], [PType], [Tier], [NewRate]
order by [PType]
