SQL Pivot For Multiple Metrics By Year

Is there a way to show multiple metrics in 1 SQL pivot operator. Basically, I have the Table1 and want the desired results is the Table2 format. Table1 ACCOUNTS YEAR REVENUE

Solution 1:

DECLARE @t TABLE
    (
      ACCOUNTS NVARCHAR(MAX) ,
      YEAR INT ,
      REVENUE INT ,
      MARGIN INT
    )

INSERT  INTO @t
VALUES ('ACCOUNT1', 2012, 100, 50 ),('ACCOUNT1', 2013, 104, 52 ),
       ('ACCOUNT1', 2014, 108, 54 ),('ACCOUNT2', 2012, 112, 56 ),
       ('ACCOUNT2', 2013, 116, 58 ),('ACCOUNT2', 2014, 120, 60 ),
       ('ACCOUNT3', 2012, 124, 62 ),('ACCOUNT3', 2013, 128, 64 ),
       ('ACCOUNT3', 2014, 132, 66 )

;WITH CTE AS
(
  SELECT ACCOUNTS, value, name + '_' + cast(YEAR as char(4)) header
  FROM @t as p  
  UNPIVOT      
  (value FOR name IN           
  ([REVENUE], [MARGIN]) ) AS unpvt  
)
   SELECT ACCOUNTS, [REVENUE_2012],[REVENUE_2013],[REVENUE_2014]
         ,[MARGIN_2012],[MARGIN_2013],[MARGIN_2014] 
   FROM CTE
   PIVOT
   (SUM([value])  
   FOR header
   in([REVENUE_2012],[REVENUE_2013],[REVENUE_2014], [MARGIN_2012]
      ,[MARGIN_2013],[MARGIN_2014])  
   )AS p ORDER BY 2,3,4

Result:

ACCOUNTS REVENUE_2012 REVENUE_2013 REVENUE_2014 MARGIN_2012 MARGIN_2013 MARGIN_2014
ACCOUNT1 100          104          108          50          52          54
ACCOUNT2 112          116          120          56          58          60
ACCOUNT3 124          128          132          62          64          66

Solution 2:

You could do something like this:

SELECT
    Table1.ACCOUNTS,
    SUM(CASE WHEN Table1.[YEAR]=2012 THEN Table1.REVENUE ELSE 0 END) AS REVENUE_2012,
    SUM(CASE WHEN Table1.[YEAR]=2013 THEN Table1.REVENUE ELSE 0 END) AS REVENUE_2013,
    SUM(CASE WHEN Table1.[YEAR]=2014 THEN Table1.REVENUE ELSE 0 END) AS REVENUE_2014,
    SUM(CASE WHEN Table1.[YEAR]=2012 THEN Table1.MARGIN ELSE 0 END) AS MARGIN_2012,
    SUM(CASE WHEN Table1.[YEAR]=2013 THEN Table1.MARGIN ELSE 0 END) AS MARGIN_2013,
    SUM(CASE WHEN Table1.[YEAR]=2014 THEN Table1.MARGIN ELSE 0 END) AS MARGIN_2014
FROM
    Table1
GROUP BY
    Table1.ACCOUNTS

Solution 3:

DECLARE @t TABLE
    (
      ACCOUNTS NVARCHAR(MAX) ,
      YEAR INT ,
      REVENUE INT ,
      MARGIN INT
    )

INSERT  INTO @t
VALUES  ( 'ACCOUNT1', 2012, 100, 50 ),
        ( 'ACCOUNT1', 2013, 104, 52 ),
        ( 'ACCOUNT1', 2014, 108, 54 ),
        ( 'ACCOUNT2', 2012, 112, 56 ),
        ( 'ACCOUNT2', 2013, 116, 58 ),
        ( 'ACCOUNT2', 2014, 120, 60 ),
        ( 'ACCOUNT3', 2012, 124, 62 ),
        ( 'ACCOUNT3', 2013, 128, 64 ),
        ( 'ACCOUNT3', 2014, 132, 66 );
WITH    cte1
          AS ( SELECT   ACCOUNTS ,
                        YEAR ,
                        REVENUE
               FROM     @t
             ),
        cte2
          AS ( SELECT   ACCOUNTS ,
                        YEAR ,
                        MARGIN
               FROM     @t
             )
    SELECT  a1.* ,
            a2.[2012] ,
            a2.[2013] ,
            a2.[2014]
    FROM    ( SELECT    *
              FROM      cte1 PIVOT( SUM(REVENUE) FOR YEAR IN ( [2012], [2013], [2014] ) ) AS p
            ) a1
            JOIN ( SELECT   *
                   FROM     cte2 PIVOT( SUM(MARGIN) FOR YEAR IN ( [2012], [2013], [2014] ) ) AS p
                 ) a2 ON a1.ACCOUNTS = a2.ACCOUNTS

Output:

ACCOUNTS    2012    2013    2014    2012    2013    2014
ACCOUNT1    100     104     108     50      52      54
ACCOUNT2    112     116     120     56      58      60
ACCOUNT3    124     128     132     62      64      66