Pivot query to return multiple repeating groups?

Question

I'm trying to get a result set (which will be inserted into a table) that has multiple repeating groups. Here's a script that shows a very simplified version of the data I'm starting out with:

CREATE TABLE #Aggregate(
    StoreKey int ,
    NumberOfDaysBack int ,
    ThisYearGrossTransactions int ,
    ThisYearGrossPrice money ,
    LastYearGrossTransactions int ,
    LastYearGrossPrice money 
) 
GO
INSERT #Aggregate VALUES (10134, 7, 198, 71324.3600, 248, 95889.6089)
INSERT #Aggregate VALUES (10131, 7, 9, 1299.8300, 3, 662.5700)
INSERT #Aggregate VALUES (10132, 7, 57, 11029.5300, 56, 6848.3800)
INSERT #Aggregate VALUES (10130, 7, 6, 429.3100, 15, 1606.1100)
INSERT #Aggregate VALUES (10134, 28, 815, 339315.9265, 822, 342834.2365)
INSERT #Aggregate VALUES (10131, 28, 29, 5725.4900, 8, 1938.4100)
INSERT #Aggregate VALUES (10132, 28, 262, 42892.5476, 269, 37229.2600)
INSERT #Aggregate VALUES (10130, 28, 62, 6427.7072, 93, 13428.0000)

And then I'd like to show separate sets of data for each set of NumberOfDaysBack, like this:

StoreKey    ThisYearLast7GrossTransactions ThisYearLast7GrossPrice LastYearLast7GrossTransactions LastYearLast7GrossPrice ThisYearLast28GrossTransactions ThisYearLast28GrossPrice LastYearLast28GrossTransactions LastYearLast28GrossPrice
----------- ------------------------------ ----------------------- ------------------------------ ----------------------- ------------------------------- ------------------------ ------------------------------- ------------------------
10130       6                              429.31                  15                             1606.11                 62                              6427.7072                93                              13428.00
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

I was able to get the above result set with this query.

-- (using this Common Table expression as a shortcut, there's actually a dimention table
;with Store as (select distinct StoreKey from #Aggregate)
Select
    Store.StoreKey
    ,ThisYearLast7GrossTransactions = DaysBack7.ThisYearGrossTransactions
    ,ThisYearLast7GrossPrice = DaysBack7.ThisYearGrossPrice
    ,LastYearLast7GrossTransactions = DaysBack7.LastYearGrossTransactions
    ,LastYearLast7GrossPrice = DaysBack7.LastYearGrossPrice
    ,ThisYearLast28GrossTransactions = DaysBack28.ThisYearGrossTransactions
    ,ThisYearLast28GrossPrice = DaysBack28.ThisYearGrossPrice
    ,LastYearLast28GrossTransactions = DaysBack28.LastYearGrossTransactions
    ,LastYearLast28GrossPrice = DaysBack28.LastYearGrossPrice    
from Store 
    join #Aggregate DaysBack7
        on Store .StoreKey = DaysBack7.StoreKey
        and DaysBack7 .NumberOfDaysBack = 7
    join #Aggregate DaysBack28
        on Store .StoreKey = DaysBack28.StoreKey
        and DaysBack28 .NumberOfDaysBack = 28
order by Store.StoreKey

However, since my actual data set is far more complicated, with many more NumberOfDaysBack and many more metrics that may change, I'd like to be able to do this with a pivot statement, without needing to explicitly name each field.

Is this possible? Thanks for any ideas!

Taryn · Accepted Answer · 2012-11-20T10:58:28.710

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.

Very impressive! Thanks for putting time into this. Your solution looks spot-on. Also I'd never heard of the SQLFiddle site - awesome! — Sylvia, Nov 20 '12 at 15:08

score 1 · Answer 2 · edited May 23 '17 at 12:27

The result what you would like to achive, could be something like this useing PIVOT:

SELECT StoreKey, 
[1] AS ThisYearGrossTransactionsFor7Days, 
[2] AS ThisYearGrossPriceFor7Days, 
[3] AS LastYearGrossTransactionsFor7Days, 
[4] AS LastYearGrossPriceFor7Days, 
[5] AS ThisYearGrossTransactionsFor28Days, 
[6] AS ThisYearGrossPriceFor28Days, 
[7] AS LastYearGrossTransactionsFor28Days, 
[8] AS LastYearGrossPriceFor28Days
FROM
(SELECT StoreKey,ThisYearGrossTransactions AS Value, 1 AS TypeOfAggregate
FROM #Aggregate WHERE NumberOfDaysBack = 7
UNION ALL 
SELECT StoreKey,ThisYearGrossPrice AS Value, 2 AS TypeOfAggregate
FROM #Aggregate WHERE NumberOfDaysBack = 7
UNION ALL 
SELECT StoreKey,LastYearGrossTransactions AS Value, 3 AS TypeOfAggregate
FROM #Aggregate WHERE NumberOfDaysBack = 7
UNION ALL 
SELECT StoreKey,LastYearGrossPrice AS Value, 4 AS TypeOfAggreagate
FROM #Aggregate WHERE NumberOfDaysBack = 7
UNION ALL
SELECT StoreKey,ThisYearGrossTransactions AS Value, 5 AS TypeOfAggregate
FROM #Aggregate WHERE NumberOfDaysBack = 28
UNION ALL 
SELECT StoreKey,ThisYearGrossPrice AS Value, 6 AS TypeOfAggregate
FROM #Aggregate WHERE NumberOfDaysBack = 28
UNION ALL 
SELECT StoreKey,LastYearGrossTransactions AS Value, 7 AS TypeOfAggregate
FROM #Aggregate WHERE NumberOfDaysBack = 28
UNION ALL 
SELECT StoreKey,LastYearGrossPrice AS Value, 8 AS TypeOfAggregate
FROM #Aggregate WHERE NumberOfDaysBack = 28) p
PIVOT(
SUM(Value)
FOR TypeOfAggregate IN ([1], [2], [3], [4], [5], [6], [7], [8])
) AS pvt
ORDER BY StoreKey

For this, as you can see, still need to transform the #Aggregate to a different format, and "type" the values (TypeOfAggregate).

BUT for this, you can write a Dynamic PIVOT. Here is a thread about it on StackOwerflow

Hi Andras - thanks for looking at this. Sorry, I'm not quite understanding your answer, also it gives an error when trying to run it - Invalid column name 'TypeOfAggreagate'. — Sylvia, Nov 19 '12 at 15:51
@Sylvia Sorry there was a typo in my code, I fixed it, could yo try again? — András Ottó, Nov 19 '12 at 16:01
Got it. It looks like this does achieve the same results, with a pivot statement instead of using multiple joins. However, in terms of readability and (perhaps, would need to test) performance, I'm not sure it improves on the original query. In any case, I'm marking this as the answer because it does do what I asked, using the pivot statement. Thanks Andras! — Sylvia, Nov 20 '12 at 08:01

Pivot query to return multiple repeating groups?

2 Answers2

Linked