CODEWITHSUNDEEP
sqlsql-servert-sql
moe
moe

Reputation: 5249

using if else in sql server ( un pivot)

I want to run only one sql statement based on the product id:

If product id = 0 then I want to run this sql statement:

SELECT QuarterName as Quarters, QuarterValue
  FROM
    (
       SELECT month_key,
         sum(Q1)Q1, sum(Q2)Q2, sum(Q3)Q3, sum(Q4)Q4
          FROM myTable
          where line_item_id = '2548' 
            group by month_key 
            ) stu
                 UNPIVOT
                    (QuarterValue FOR QuarterName IN (Q1, Q2, Q3, Q4)
                     ) AS QTR 
                        where month_key =2016

If product id <> 0 then I want to run this sql statement:

SELECT QuarterName as Quarters, QuarterValue
  FROM
    (
       SELECT month_key,
         sum(Q1)Q1, sum(Q2)Q2, sum(Q3)Q3, sum(Q4)Q4
          FROM myTable
          where line_item_id = '2548' 
          and product_id = 'wq2745'
            group by month_key 
            ) stu
                 UNPIVOT
                    (QuarterValue FOR QuarterName IN (Q1, Q2, Q3, Q4)
                     ) AS QTR 
                        where month_key =2016

The difference is the first query does not use the product id in the where clause but the 2nd one does. I am running these sql queries in C# application so the product id value is coming as a parameters based on some drop down boxes.

Upvotes: 0

Views: 63

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

You don't need two distinct queries here. You can easily handle this with a case expression. Something like this.

SELECT QuarterName as Quarters, QuarterValue
  FROM
    (
       SELECT month_key,
         sum(Q1)Q1, sum(Q2)Q2, sum(Q3)Q3, sum(Q4)Q4
          FROM myTable
          where line_item_id = '2548' 
          and product_id = case @ProductId when <> 0 then 'wq2745' else product_id end
            group by month_key 
            ) stu
                 UNPIVOT
                    (QuarterValue FOR QuarterName IN (Q1, Q2, Q3, Q4)
                     ) AS QTR 
                        where month_key =2016

Upvotes: 0

SqlZim
SqlZim

Reputation: 38043

Using isnull(nullif()):

declare @ProductId varchar(6);
  set @ProductId = 'wq2745' 

begin;
  select 
      Quarters = QuarterName
    , QuarterValue
  from (
   select month_key
    , sum(Q1) Q1
    , sum(Q2) Q2
    , sum(Q3) Q3
    , sum(Q4) Q4
   from myTable
   where line_item_id = '2548' 
    and product_id = isnull(nullif(@ProductId,'0'),product_id)
   group by month_key
   ) stu
  unpivot(QuarterValue for QuarterName in (Q1, Q2, Q3, Q4)) as QTR
  where month_key = 2016
end;

or using If and two statements this way:

declare @ProductId varchar(6);
  set @ProductId = 'wq2745' 

if @ProductId = '0'
begin;
  select 
      Quarters = QuarterName
    , QuarterValue
  from (
   select month_key
    , sum(Q1) Q1
    , sum(Q2) Q2
    , sum(Q3) Q3
    , sum(Q4) Q4
   from myTable
   where line_item_id = '2548'
   group by month_key
   ) stu
  unpivot(QuarterValue for QuarterName in (Q1, Q2, Q3, Q4)) as QTR
  where month_key = 2016
end;

if @ProductId != '0'
begin;
  select QuarterName as Quarters
   , QuarterValue
  from (
   select month_key
    , sum(Q1) Q1
    , sum(Q2) Q2
    , sum(Q3) Q3
    , sum(Q4) Q4
   from myTable
   where line_item_id = '2548' 
    and product_id = @ProductId
   group by month_key
   ) stu
  unpivot(QuarterValue for QuarterName in (Q1, Q2, Q3, Q4)) as QTR
  where month_key = 2016
end;

Upvotes: 1

Related Questions