Pivot query to return multiple repeating groups?

pivot, sql-server, sql-server-2008, t-sql, unpivot

Solution

You can get the result that you want using both `UNPIVOT` and `PIVOT`:

select *
from
(
  select storekey, 
    value, col +'Last'+ cast(numberofdaysback as varchar(20)) + 'Days' new_col
  from
  (
    select storekey,
      numberofdaysback,
      cast(ThisYearGrossTransactions as decimal(20,5)) ThisYearGrossTransactions,
      cast(ThisYearGrossPrice as decimal(20,5)) ThisYearGrossPrice,
      cast(LastYearGrossTransactions as decimal(20,5)) LastYearGrossTransactions,
      cast(LastYearGrossPrice as decimal(20,5)) LastYearGrossPrice    
    from aggregate
  ) un
  unpivot
  (
    value
    for col in (ThisYearGrossTransactions, ThisYearGrossPrice,
                LastYearGrossTransactions, LastYearGrossPrice)
  ) unpiv
) src
pivot
(
  sum(value)
  for new_col in ([ThisYearGrossTransactionsLast7Days], [ThisYearGrossPriceLast7Days],
                  [LastYearGrossTransactionsLast7Days], [LastYearGrossPriceLast7Days],
                  [ThisYearGrossTransactionsLast28Days], [ThisYearGrossPriceLast28Days],
                  [LastYearGrossTransactionsLast28Days], [LastYearGrossPriceLast28Days])
) piv;

See SQL Fiddle with Demo

The `UNPIVOT` takes the column values in `ThisYearGrossTransactions`, `ThisYearGrossPrice`, `LastYearGrossTransactions` and `LastYearGrossPrice` and converts them into a single column with multiple rows.

select storekey, 
  value, col +'Last'+ cast(numberofdaysback as varchar(20)) + 'Days' new_col
from
(
  select storekey,
    numberofdaysback,
    cast(ThisYearGrossTransactions as decimal(20,5)) ThisYearGrossTransactions,
    cast(ThisYearGrossPrice as decimal(20,5)) ThisYearGrossPrice,
    cast(LastYearGrossTransactions as decimal(20,5)) LastYearGrossTransactions,
    cast(LastYearGrossPrice as decimal(20,5)) LastYearGrossPrice    
  from aggregate
) un
unpivot
(
  value
  for col in (ThisYearGrossTransactions, ThisYearGrossPrice,
              LastYearGrossTransactions, LastYearGrossPrice)
) unpiv

See SQL Fiddle with Demo

A requirement of the `UNPIVOT` is that all of the datatypes must be the same so you need to apply either `cast` or `convert` to any values. Then to `PIVOT` the data, I created the new column names by adding the `numberofdaysback` to each record. THese are the values that are used in the `PIVOT` portion of the query.

The final result is:

| STOREKEY | THISYEARGROSSTRANSACTIONSLAST7DAYS | THISYEARGROSSPRICELAST7DAYS | LASTYEARGROSSTRANSACTIONSLAST7DAYS | LASTYEARGROSSPRICELAST7DAYS | THISYEARGROSSTRANSACTIONSLAST28DAYS | THISYEARGROSSPRICELAST28DAYS | LASTYEARGROSSTRANSACTIONSLAST28DAYS | LASTYEARGROSSPRICELAST28DAYS |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|    10130 |                                  6 |                      429.31 |                                 15 |                     1606.11 |                                  62 |                    6427.7072 |                                  93 |                        13428 |
|    10131 |                                  9 |                     1299.83 |                                  3 |                      662.57 |                                  29 |                      5725.49 |                                   8 |                      1938.41 |
|    10132 |                                 57 |                    11029.53 |                                 56 |                     6848.38 |                                 262 |                   42892.5476 |                                 269 |                     37229.26 |
|    10134 |                                198 |                    71324.36 |                                248 |                  95889.6089 |                                 815 |                  339315.9265 |                                 822 |                  342834.2365 |

The static version above works great if you have a known number of values for `NumberOfDaysBack` but if you have an unknown number of many values, then you can use a dynamic version of this:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)

select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('Aggregate') and
               C.name not in ('StoreKey', 'NumberOfDaysBack')
         for xml path('')), 1, 1, '')

select @colsPivot = STUFF((SELECT  ',' 
                      + quotename(c.name +'Last'
                         + cast(a.NumberOfDaysBack as varchar(10)) +'Days')
                    from Aggregate a
                    cross apply sys.columns  C
                   where C.object_id = object_id('Aggregate') and
                         C.name not in ('StoreKey', 'NumberOfDaysBack')
                   group by c.name, a.NumberOfDaysBack
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select *
      from
      (
        select storekey, 
            value, col +''Last''+ cast(numberofdaysback as varchar(20)) + ''Days'' new_col
        from 
        (
          select storekey,
            numberofdaysback,
            cast(ThisYearGrossTransactions as decimal(20,5)) ThisYearGrossTransactions,
            cast(ThisYearGrossPrice as decimal(20,5)) ThisYearGrossPrice,
            cast(LastYearGrossTransactions as decimal(20,5)) LastYearGrossTransactions,
            cast(LastYearGrossPrice as decimal(20,5)) LastYearGrossPrice    
          from aggregate
        ) x
        unpivot
        (
          value
          for col in ('+ @colsunpivot +')
        ) u
      ) x1
      pivot
      (
        sum(value)
        for new_col in ('+ @colspivot +')
      ) p'

exec(@query)

See SQL Fiddle with Demo

The result will be the same with both queries.

Problem

Original source

Related problems

• SQL Server dynamic PIVOT query?