CODEWITHSUNDEEP
sql-servert-sqlpivotpivot-table
Jaime
Jaime

Reputation: 159

Pivot on multiple columns (T-SQL)

I have this set of data (list of actions), already cleaned and ready for the PIVOT operation.

enter image description here

How can I achieve something like this (aggregating the nº of actions; duration; gross value; net value... BY month)?

enter image description here

(preparing for the MS Certification and quite frustrated because I can't solve this using PIVOT)

Upvotes: 3

Views: 9501

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

First you need to unpivot the data then you can pivot the result

Sample data 

  create table piv(TimeRange varchar(50),Type varchar(50), Month int,ActionDuration int, GrossValue bigint, NetValue bigint)

  insert piv values
  ('09:00-10:00','Bonus'        ,1 ,30 ,0       ,0       ), 
  ('09:00-10:00','Bonus'        ,1 ,30 ,0       ,0       ),
  ('09:00-10:00','Billed'       ,1 ,30 ,77982   ,701838  ),
  ('09:00-10:00','Not Billed'   ,1 ,30 ,506124  ,4555116 ),
  ('10:00-11:00','Bonus'        ,1 ,30 ,0       ,0       ),
  ('10:00-11:00','Billed'       ,1 ,30 ,109739  ,987651  ),
  ('10:00-11:00','Billed'       ,1 ,30 ,109739  ,987651  ),
  ('10:00-11:00','Not Billed'   ,1 ,30 ,98021   ,882189  ),
  ('09:00-10:00','Bonus'        ,2 ,30 ,0       ,0       ),
  ('09:00-10:00','Billed'       ,2 ,30 ,288947  ,2600523 ),
  ('09:00-10:00','Billed'       ,2 ,30 ,288947  ,2600523 ),
  ('09:00-10:00','Not Billed'   ,2 ,30 ,64669   ,582021  ),
  ('10:00-11:00','Bonus'        ,2 ,30 ,0       ,0       ),
  ('10:00-11:00','Billed'       ,2 ,30 ,48738   ,438642  ),
  ('10:00-11:00','Not Billed'   ,2 ,30 ,269969  ,2429721 )

Query 

SELECT *
FROM   (SELECT TimeRange,
               TYPE,
               DATA,
               left(DateName( month , DateAdd( month , month , 0 ) - 1 ),3) + ' '
               + COLUMN_NAME AS PIV_COL
        FROM   Yourtable
               CROSS APPLY (VALUES ('ActionDuration',ActionDuration),
                                   ('GrossValue',GrossValue),
                                   ('NetValue',NetValue)) CS(COLUMN_NAME, DATA)) a
       PIVOT (sum(DATA)
             FOR PIV_COL IN([Jan ActionDuration],
                            [Jan GrossValue],
                            [Jan NetValue],
                            [Feb ActionDuration],
                            [Feb GrossValue],
                            [Feb NetValue])) PV

Result

TimeRange   TYPE        Jan ActionDuration  Jan GrossValue  Jan NetValue    Feb ActionDuration  Feb GrossValue  Feb NetValue
----------- ----------- ------------------  --------------  ------------    ------------------  --------------  -------------
09:00-10:00 Billed      30                  77982           701838          60                  577894              5201046
10:00-11:00 Billed      60                  219478          1975302         30                  48738               438642
09:00-10:00 Bonus       60                  0               0               30                  0                   0
10:00-11:00 Bonus       30                  0               0               30                  0                   0
09:00-10:00 Not Billed  30                  506124          4555116         30                  64669               582021
10:00-11:00 Not Billed  30                  98021           882189          30                  269969              2429721

Upvotes: 7

Related Questions