March 5, 2010 at 2:40 pm
Okay so I've assigned variables to pivot queries before like so:
declare @Q2Sales int,@Q3Sales int,@Q4Sales int
select
@Q2Sales = sum(Case WHen Qt = 2 Then Cost Else 0 END),
@Q3Sales = sum(Case WHen Qt = 3 Then Cost Else 0 END),
@Q4Sales = sum(Case WHen Qt = 4 Then Cost Else 0 END)
from (
select 132.23 as Cost,2 as Qt, 2009 as Yr
UNION
select 908.23 as Cost,4 as Qt, 2009 as Yr
UNION
select 568.23 as Cost,3 as Qt, 2009 as Yr
) a
Group by Yr
This works well with numeric values but how I'd like to accomplish something similar with varchar values. I can't SUM the case statement b/c it's a varchar. How else would this be accomplished?
select
@Zip1 = Case When Rank = 1 Then Zip End,
@Zip2 = Case When Rank = 2 Then Zip End,
@Zip3 = Case When Rank = 3 Then Zip End
from (
select '32378' as Zip,1 as rank
UNION
select '90210' as Zip,2 as rank
UNION
select '10001' as Zip,3 as rank) a
March 5, 2010 at 3:55 pm
Instead of using the SUM() function on varchar values you'd need to use MAX().
Something like:
select
@Zip1 = max(Case When Rank = 1 Then Zip End),
@Zip2 = max(Case When Rank = 2 Then Zip End),
@Zip3 = max(Case When Rank = 3 Then Zip End)
from (
select '32378' as Zip,1 as rank
UNION
select '90210' as Zip,2 as rank
UNION
select '10001' as Zip,3 as rank) a
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply