Displaying Columns as Rows in SQL Server 2005

Question

I have read dozens of solutions to similar transposition problems as the one I am about to propose but oddly none that exactly mirrors my issue. I am simply trying to flip my rows to columns in a simple dashboard type data set.

The data when pulled from various transaction tables looks like this:

DatePeriod  PeriodNumberOverall   Transactions   Customers   Visits

'Jan 2012'   1                    100            50          150
'Feb 2012'   2                    200            100         300
'Mar 2012'   3                    300            200         600

and I want to be able to generate the following:

                      Jan 2012   Feb 2012   Mar 2012

Transactions          100        200        300
Customers             50         100        200
Visits                150        300        600

The metrics will be static (Transactions, Customers and Visits), but the date periods will be dynamic (IE - more added as months go by).

Again, I have ready many examples leveraging pivot, unpivot, store procedures, UNION ALLs, etc, but nothing where I am not doing any aggregating, just literally transposing the whole output. I have also found an easy way to do this in Visual Studio 2005 using a matrix with an embedded list, but I can't export the final output to excel which is a requirement. Any help would be greatly appreciated.

Answer 1

You need to dynamically create a SQL statement with PIVOT and APPLY operators on the fly and then run that command. If your metrics static(Transactions, Customers and Visits), hence we can use CROSS APPLY operator with VALUES As a Table Source.

For SQL Server2008+

DECLARE @cols nvarchar( max),
        @query nvarchar(max)
SELECT @cols =
  STUFF((SELECT ',' + QUOTENAME(t.DatePeriod) AS ColName                               
         FROM dbo.test62 t                   
         FOR XML PATH(''), TYPE).value ('.', 'nvarchar(max)'), 1, 1, '')     

SET @query =
 'SELECT *
  FROM (
        SELECT t.DatePeriod, COALESCE(o.Transactions, o.Customers, o.Visits) AS PvtVals, o.PvtColumns, o.OrderColumns 
        FROM dbo.test62 t CROSS APPLY (
                                       VALUES(t.Transactions, NULL, NULL, ''Transaction'', 1),
                                             (NULL, t.Customers, NULL, ''Customers'', 2),
                                             (NULL, NULL, t.Visits, ''Visits'', 3)
                                       ) o (Transactions, Customers, Visits, PvtColumns, OrderColumns)
        ) p
  PIVOT
   (      
    MAX(PvtVals) FOR DatePeriod IN (' + @cols + ')
    ) AS pvt
  ORDER BY pvt.OrderColumns '
EXEC(@query) 

Result:

PvtColumns  Jan 2012 Fed 2012 Mar 2012
Transaction 100      200      300
Customers   50       100      200
Visits      150      300      600

Demo on SQLFiddle

For SQL Server 2005

DECLARE @cols nvarchar( max),
        @query nvarchar(max)
SELECT @cols =
  STUFF((SELECT ',' + QUOTENAME(t.DatePeriod) AS ColName                               
         FROM dbo.test62 t                   
         FOR XML PATH(''), TYPE).value ('.', 'nvarchar(max)'), 1, 1, '')     

SET @query =
 'SELECT *
  FROM (
        SELECT t.DatePeriod, COALESCE(o.Transactions, o.Customers, o.Visits) AS PvtVals, o.PvtColumns, o.OrderColumns
        FROM dbo.test62 t CROSS APPLY (
                                       SELECT t.Transactions, NULL, NULL, ''Transaction'', 1
                                       UNION ALL 
                                       SELECT NULL, t.Customers, NULL, ''Customers'', 2
                                       UNION ALL 
                                       SELECT NULL, NULL, t.Visits, ''Visits'', 3
                                       ) o (Transactions, Customers, Visits, PvtColumns, OrderColumns)
        ) p
  PIVOT
   (      
    MAX(PvtVals) FOR DatePeriod IN (' + @cols + ')
    ) AS pvt
  ORDER BY pvt.OrderColumns'
EXEC(@query) 

Answer 2

In order to get the result that you want you need to first UNPIVOT the data and then PIVOT theDatePeriod` Values.

The UNPIVOT will transform the multiple columns of Transactions, Customers and Visits into multiple rows. The other answers are using a UNION ALL to unpivot but SQL Server 2005 was the first year the UNPIVOT function was supported.

The query to unpivot the data is:

select dateperiod,
  col, value
from transactions
unpivot
(
  value for col in (Transactions, Customers, Visits)
) u

See Demo. This transforms your current columns into multiple rows, so the data looks like the following:

| DATEPERIOD |          COL | VALUE |
-------------------------------------
|   Jan 2012 | Transactions |   100 |
|   Jan 2012 |    Customers |    50 |
|   Jan 2012 |       Visits |   150 |
|   Feb 2012 | Transactions |   200 |

Now, since the data is in rows, you can apply the PIVOT function to the DatePeriod column:

select col, [Jan 2012], [Feb 2012], [Mar 2012]
from
(
  select dateperiod,
    t.col, value, c.SortOrder
  from
  (
    select dateperiod,
      col, value
    from transactions
    unpivot
    (
      value for col in (Transactions, Customers, Visits)
    ) u
  ) t
  inner join
  (
    select 'Transactions' col, 1 SortOrder
    union all
    select 'Customers' col, 2 SortOrder
    union all
    select 'Visits' col, 3 SortOrder
   ) c
    on t.col = c.col
) d
pivot
(
  sum(value)
  for dateperiod in ([Jan 2012], [Feb 2012], [Mar 2012])
) piv
order by SortOrder;

See SQL Fiddle with Demo.

If you have an unknown number of date period's then you will use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(dateperiod) 
                    from transactions
                    group by dateperiod, PeriodNumberOverall
                    order by PeriodNumberOverall
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT col, ' + @cols + ' 
             from 
             (
                select dateperiod,
                  t.col, value, c.SortOrder
                from
                (
                  select dateperiod,
                    col, value
                  from transactions
                  unpivot
                  (
                    value for col in (Transactions, Customers, Visits)
                  ) u
                ) t
                inner join
                (
                  select ''Transactions'' col, 1 SortOrder
                  union all
                  select ''Customers'' col, 2 SortOrder
                  union all
                  select ''Visits'' col, 3 SortOrder
                 ) c
                  on t.col = c.col
            ) x
            pivot 
            (
                sum(value)
                for dateperiod in (' + @cols + ')
            ) p 
            order by SortOrder'

execute(@query)

See SQL Fiddle with Demo. Both will give the result:

|          COL | JAN 2012 | FEB 2012 | MAR 2012 |
-------------------------------------------------
| Transactions |      100 |      200 |      300 |
|    Customers |       50 |      100 |      200 |
|       Visits |      150 |      300 |      600 |

Answer 3

If you can know how many different date period in advance, then you can use fixed query like following:

;with CTE_UNIONTable
as 
(
select [DatePeriod],[PeriodNumberOverall],[Transactions] as [value], 'Transactions' as subType from table1
UNION ALL 
select [DatePeriod],[PeriodNumberOverall],[Customers] as [value], 'Customers' as subType from table1
UNION ALL 
select [DatePeriod],[PeriodNumberOverall],[Visits] as [value], 'Visits' as subType from table1
), CTE_MiddleResult
as 
(
select * from CTE_UNIONTable
    pivot 
    (
       max(value) 
       for DatePeriod in ([Jan 2012],[Feb 2012],[Mar 2012])
     ) as P
     )
select SubType, max([Jan 2012]) as [Jan 2012] ,max([Feb 2012]) as [Feb 2012], max([Mar 2012]) as [Feb 2012]
from CTE_MiddleResult
group by SubType

SQL FIDDLE DEMO

If how many date period is unpredictable, then @Alexander already gave the solution, the following code is just a second opinion, instead of using APPLY, using UNION ALL

DECLARE @cols nvarchar( max),
        @query nvarchar (max),
        @selective nvarchar(max)
SELECT @cols =
  STUFF((SELECT ',' + QUOTENAME(t.DatePeriod) AS ColName                               
         FROM table1 t                   
         FOR XML PATH( ''), TYPE).value ('.', 'nvarchar(max)'),1,1,'')

SELECT @selective =
  STUFF((SELECT ',MAX(' + QUOTENAME(t.DatePeriod) +') as ' + QUOTENAME(t.DatePeriod)  AS ColName                               
         FROM table1 t                   
         FOR XML PATH( ''), TYPE).value ('.', 'nvarchar(max)'),1,1,'')

set @query = '
;with CTE_UNIONTable
as 
(
select [DatePeriod],[PeriodNumberOverall],[Transactions] as [value], ''Transactions'' as subType from table1
UNION ALL 
select [DatePeriod],[PeriodNumberOverall],[Customers] as [value], ''Customers'' as subType from table1
UNION ALL 
select [DatePeriod],[PeriodNumberOverall],[Visits] as [value], ''Visits'' as subType from table1
), CTE_MiddleResult
as 
(
select * from CTE_UNIONTable
    pivot 
    (
       max(value) 
       for DatePeriod in ('+@cols+')
     ) as P
)
select SubType,' + @selective + ' 
from CTE_MiddleResult
group by SubType'

exec(@query)

SQL FIDDLE DEMO