how to use case inside pivot

• UnionAll

  SSCrazy

  Points: 2102

  More actions

  August 17, 2009 at 8:09 am

  #97873

  Hi ,

  I am wondering if case is allowed in pivot.

  For example,

  instead of directly doing pivot( sum(Amt1) for Col1 in (a,b,c))

  i want to do it this way...

  pivot ( case when @Var = 'TBT' then sum(Amt1)

  when @Var = 'BBT' then sum(Amt2) end

  for Col1 in (a,b,c))

  Is this Possible?

  Can you suggest me a way to acheive this?

  Thanks,

• Eddie Wuerch

  SSChampion

  Points: 12536

  More actions

  August 17, 2009 at 3:28 pm

  #1040544

  Add the CASE to the query before the PIVOT (the one that defines the data that is pivoted):

  CASE WHEN @Var = 'TBT' THEN Amt1

  WHEN @Var = 'BBT' THEN Amt2 ELSE 0 END AS PivotAmt

  Then, pivot on the calculated value:

  PIVOT SUM(PivotAmt) FOR Col1 IN (a,b,c)

  -Eddie

  Eddie Wuerch
  MCM: SQL