Сводный запрос для возврата нескольких повторяющихся групп?
Я пытаюсь получить набор результатов (который будет вставлен в таблицу), который имеет несколько повторяющихся групп. Вот скрипт, который показывает очень упрощенную версию данных, с которых я начинаю:
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)
И затем я хотел бы показать отдельные наборы данных для каждого набора NumberOfDaysBack, например:
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
Мне удалось получить вышеуказанный набор результатов с этим запросом.
-- (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
Однако, поскольку мой фактический набор данных намного сложнее, с большим количеством NumberOfDaysBack и множеством других метрик, которые могут измениться, я хотел бы иметь возможность сделать это с помощью оператора сводной таблицы, без необходимости явно называть каждое поле.
Это возможно? Спасибо за любые идеи!
2 ответа
Вы можете получить желаемый результат, используя оба UNPIVOT а также 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;
Смотрите SQL Fiddle с демо
UNPIVOT принимает значения столбца в ThisYearGrossTransactions, ThisYearGrossPrice, LastYearGrossTransactions а также LastYearGrossPrice и преобразует их в один столбец с несколькими строками.
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
Смотрите SQL Fiddle с демо
Требование UNPIVOT является то, что все типы данных должны быть одинаковыми, поэтому вы должны применить либо cast или же convert к любым ценностям. Тогда к PIVOT данные, я создал новые имена столбцов, добавив numberofdaysback к каждой записи. Это те значения, которые используются в PIVOT часть запроса.
Окончательный результат:
| 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 |
Статическая версия выше прекрасно работает, если у вас есть известное количество значений для NumberOfDaysBack но если у вас есть неизвестное количество значений, вы можете использовать динамическую версию этого:
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)
Смотрите SQL Fiddle с демо
Результат будет одинаковым с обоими запросами.
Результат, которого вы хотели бы достичь, может быть примерно таким, используя 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
Для этого, как видите, еще нужно преобразовать #Aggregate в другой формат и "введите" значения (TypeOfAggregate).
НО для этого можно написать Dynamic PIVOT, Вот нить об этом на StackOwerflow