CASE Statement using Dynamic query

Question

Base Table:

date        category        amount
1/1/2012    ABC             1000.00
2/1/2012    DEF             500.00
2/1/2012    GHI             800.00
2/10/2012   DEF             700.00
3/1/2012    ABC             1100.00

Dynamic SQL PIVOT:

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)


DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '


execute(@query)

drop table temp

Results:

Date                        ABC         DEF    GHI
2012-01-01 00:00:00.000     1000.00     NULL    NULL
2012-02-01 00:00:00.000     NULL        500.00  800.00
2012-02-10 00:00:00.000     NULL        700.00  NULL
2012-03-01 00:00:00.000     1100.00     NULL    NULL

Question : How can we continue the above Dynamic SQL query for using CASE statement?

I want the values in the result set to be displayed as below.

Example: For ABC Column

WHEN the value is 1000.00 THEN 'HIGH'

WHEN the value is 500.00 THEN 'MEDIUM'

WHEN the value is null THEN 'LOW'.

Similarly for DEF column

WHEN the value is 1000.00 THEN 'HIGH'

WHEN the value is 500.00 THEN 'MEDIUM'

WHEN the value is null THEN 'LOW'.

And so on.., for all the columns. Also,If a new column is added to the base table.., the CASE statement has to be applicable even for that new column without making any changes to the query(I guess looping condition might be required). Could anyone help me on how to modify the above query(or) how to approach for this requirement.

score 1 · Accepted Answer · answered Jan 01 '15 at 03:33

Use Case statement while generating Pivot column list to handle the Alias.

DECLARE @cols        AS NVARCHAR(MAX),
        @query       AS NVARCHAR(MAX),
        @select_cols NVARCHAR(MAX);

SET @cols = Stuff((SELECT DISTINCT ',' + Quotename(c.category)
                   FROM   temp c
                   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')


SET @select_cols = Stuff((SELECT DISTINCT ',case when ' + Quotename(c.category)+ '= ''1000.00'' then ''High'' 
                                                 when ' + Quotename(c.category)+ '= ''500.00'' then ''Medium'' 
                                                 when ' + Quotename(c.category) + 'Is Null then ''Low''
                                                 else '+ Quotename(c.category) + ' end as '+ Quotename(c.category)
                          FROM   temp c
                          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')



SET @query = 'SELECT date, ' + @select_cols
             + ' from 
            (
                select date
                    , convert(varchar(20),amount) Amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols
             + ')
            ) p '

EXECUTE(@query)

CASE Statement using Dynamic query

I want the values in the result set to be displayed as below.

WHEN the value is null THEN 'LOW'.

WHEN the value is null THEN 'LOW'.

1 Answers1