CODEWITHSUNDEEP
sql-serverpivot
Woody
Woody

Reputation: 1787

Pivot rows to columns and columns to rows in SQL Server

I am using SQL Sever 2012. I have the following table:

CREATE TABLE #temptable
(
TimePeriod VARCHAR (50)
, Transactions INT
, SalesAmount DECIMAL (18, 2)
, NoEmployees INT
, Items INT
, Returns INT
)

INSERT INTO #temptable
VALUES
    ('ThisMonth', 5000, 2343423.41, 230, 1000, 200)
  , ('LastMonth', 6000, 433245.50, 232, 1020, 109)
  , ('ThisYear', 50000, 1391468.43, 321, 14890, 564)
  , ('LastYear', 60000, 1699713.64, 311, 16123, 342)

SELECT  *
FROM    #temptable

where I want to pivot rows to columns and column headings to rows. The table I want to end up with looks like this:

SELECT 'Transactions' Metric, 5000 ThisMonth,   6000    LastMonth, 50000 ThisYear,  60000 LastYear UNION
SELECT 'SalesAmount' Metric,    2343423.41, 433245.5,   1391468.43  ,1699713.64 UNION
SELECT 'NoEmployees' Metric,    230,    232 ,321    ,311 UNION 
SELECT 'Items'  Metric, 1000,   1020,   14890,  16123 UNION
SELECT 'Returns' Metric,    200 ,109    ,564    ,342

As a starting point I tried:

SELECT  p.ThisMonth
      , p.LastMonth
      , p.ThisYear
      , p.LastYear
FROM    #temptable
    PIVOT(   MAX(Transactions)
             FOR TimePeriod IN(ThisMonth, LastMonth, ThisYear, LastYear)) p

Upvotes: 2

Views: 72

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35563

If you unpivot, then pivot again the result can be achieved:

with cte as (
        /* this is the equivalent of an "unpivot" but using cross apply instead */
        select
            ca.*
        from #temptable
        cross apply (
            values
               (TimePeriod, 'Transactions', Transactions)
             , (TimePeriod, 'SalesAmount', SalesAmount)
             , (TimePeriod, 'NoEmployees', NoEmployees)
             , (TimePeriod, 'Items', Items)
             , (TimePeriod, 'Returns', Returns)
          ) ca (hdg, metric, val)
        )
SELECT metric,  [ThisMonth],[LastMonth], [ThisYear], [LastYear]
FROM cte
pivot (
        max([val])
        FOR [hdg] IN ([ThisMonth],[LastMonth], [ThisYear], [LastYear])
      ) p      
;

result:

          metric      ThisMonth    LastMonth    ThisYear     LastYear   
 ---- -------------- ------------ ----------- ------------ ------------ 
   1   Items             1000,00     1020,00     14890,00     16123,00  
   2   NoEmployees        230,00      232,00       321,00       311,00  
   3   Returns            200,00      109,00       564,00       342,00  
   4   SalesAmount    2343423,41   433245,50   1391468,43   1699713,64  
   5   Transactions      5000,00     6000,00     50000,00     60000,00

see: https://rextester.com/EIXT79068

OR, by using brute force:

SELECT 
      'Items' as Metric
    , max(case when TimePeriod = 'ThisMonth' then Items end) ThisMonth
    , max(case when TimePeriod = 'LastMonth' then Items end) LastMonth
    , max(case when TimePeriod = 'ThisYear'  then Items end) ThisYear
    , max(case when TimePeriod = 'LastYear'  then Items end) LastYear
FROM    #temptable
union all
SELECT 
      'NoEmployees' as Metric
    , max(case when TimePeriod = 'ThisMonth' then NoEmployees end) ThisMonth
    , max(case when TimePeriod = 'LastMonth' then NoEmployees end) LastMonth
    , max(case when TimePeriod = 'ThisYear'  then NoEmployees end) ThisYear
    , max(case when TimePeriod = 'LastYear'  then NoEmployees end) LastYear
FROM    #temptable
union all
SELECT 
      'Returns' as Metric
    , max(case when TimePeriod = 'ThisMonth' then Returns end) ThisMonth
    , max(case when TimePeriod = 'LastMonth' then Returns end) LastMonth
    , max(case when TimePeriod = 'ThisYear'  then Returns end) ThisYear
    , max(case when TimePeriod = 'LastYear'  then Returns end) LastYear
FROM    #temptable
union all
SELECT 
      'SalesAmount' as Metric
    , max(case when TimePeriod = 'ThisMonth' then SalesAmount end) ThisMonth
    , max(case when TimePeriod = 'LastMonth' then SalesAmount end) LastMonth
    , max(case when TimePeriod = 'ThisYear'  then SalesAmount end) ThisYear
    , max(case when TimePeriod = 'LastYear'  then SalesAmount end) LastYear
FROM    #temptable
union all
SELECT 
      'Transactions' as Metric
    , max(case when TimePeriod = 'ThisMonth' then transactions end) ThisMonth
    , max(case when TimePeriod = 'LastMonth' then transactions end) LastMonth
    , max(case when TimePeriod = 'ThisYear'  then transactions end) ThisYear
    , max(case when TimePeriod = 'LastYear'  then transactions end) LastYear
FROM    #temptable
;

My guess is that in altering the query you are using to get to #temptable could make this a whole lot easier

Upvotes: 1

Related Questions