Thursday, February 27, 2014

Strange problem with case statement

I got into an issue with the below statement. I don’t understand why it is not working as expected. Structure for table is below

Months Cash

1 4.13

2 46.02

3 46.02

4 5.31

5 5.31

6 51.33

7 393.53

8 393.53

9 46.02

10 51.33

11 57.82

12 32.45

13 0

14 0

15 0

select ‘Total’ as Months

,Cast(max(case when Cash = ’4.13′ then Cash end) as varchar(20)) [1]

,Cast(max(case when Cash = ’46.02′ then Cash end) as varchar(20)) [2]

,Cast(max(case when Cash = ’46.02′ then Cash end) as varchar(20)) [3]

,Cast(max(case when Cash = ’5.31′ then Cash end) as varchar(20)) [4]

,Cast(max(case when Cash = ’5.31′ then Cash end) as varchar(20)) [5]

,Cast(max(case when Cash = ’51.33′ then Cash end) as varchar(20)) [6]

,Cast(max(case when Cash = ’393.53′ then Cash end) as varchar(20)) [7]

,Cast(max(case when Cash = ’393.53′ then Cash end) as varchar(20)) [8]

,Cast(max(case when Cash = ’46.02′ then Cash end) as varchar(20)) [9]

,Cast(max(case when Cash = ’51.33′ then Cash end) as varchar(20)) [10]

,Cast(max(case when Cash = ’57.82′ then Cash end) as varchar(20)) [11]

,Cast(max(case when Cash = ’32.45′ then Cash end) as varchar(20)) [12]

,Cast(max(case when Cash = ’0′ then Cash end) as varchar(20)) [13]

,Cast(max(case when Cash = ’0′ then Cash end) as varchar(20)) [14]

,Cast(max(case when Cash = ’0′ then Cash end) as varchar(20)) [15]

from #Cash


MonthNumber 1 2 3 4 5 6 7

Total 4.13 NULL NULL NULL NULL 51.33 NULL

8 9 10 11 12 13 14 15

NULL NULL 51.33 NULL 32.45 0 0 0

Why there is null in the output? how can we resolve it?


No comments:

Post a Comment