枢轴查询返回多个重复组? [英] Pivot query to return multiple repeating groups?
问题描述
我正在尝试获取一个具有多个重复组的结果集(将被插入到表中).这是一个脚本，显示了我刚开始使用的数据的非常简化的版本:
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)
然后，我想为每组NumberOfDaysBack显示单独的数据集，如下所示:
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
但是，由于我的实际数据集要复杂得多，有更多的NumberOfDaysBack和可能更改的更多指标，因此我希望能够使用数据透视表语句来执行此操作，而无需显式命名每个字段.
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!
推荐答案
您可以同时使用UNPIVOT和PIVOT来获得所需的结果:
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;
请参见带演示的SQL提琴
UNPIVOT接受ThisYearGrossTransactions，ThisYearGrossPrice，
LastYearGrossTransactions和LastYearGrossPrice，并将它们转换为具有多行的单列.
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
请参见带有演示的SQL提琴
UNPIVOT的要求是所有数据类型必须相同，因此您需要将cast或convert应用于任何值.然后对于PIVOT数据，我通过在每个记录中添加numberofdaysback来创建新的列名称.这些是在查询的PIVOT部分中使用的值.
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.
最终结果是:
| 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的已知数量的值，则上面的静态版本非常有用，但是如果您有未知数量的许多值，则可以使用此版本的动态版本:
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)
请参见带演示的SQL提琴
两个查询的结果将相同.
The result will be the same with both queries.
这篇关于枢轴查询返回多个重复组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
