如何透视或以某种方式将行值从ProductType
转换为新列的名称并计算按 TimeId 分组的每种产品类型的金额
这是我的表格:
TimeId ProductType NetIncomeByProduct
---------------------------------------
2017121500 Casino 0.000000
2017121501 Casino 0.000000
2017121502 Casino 4.333282
2017121503 Casino 0.000000
2017121504 Casino 0.000000
2017121505 Casino 0.000000
2017121506 Casino -0.308172
2017121507 Casino -0.154086
2017121508 Casino 0.000000
2017121509 Casino 0.000000
2017121510 Casino 0.000000
2017121511 Casino -0.308172
2017121512 Casino 0.000000
2017121513 Casino 0.000000
2017121514 Casino 0.000000
2017121515 Casino 0.000000
2017121516 Casino 0.000000
2017121517 Casino 0.000000
2017121518 Casino 0.000000
2017121519 Casino 0.000000
2017121520 Casino 0.000000
2017121521 Casino 0.000000
2017121522 Casino 0.000000
2017121523 Casino 0.000000
2017121523 Live Dealer 0.000000
2017121522 Live Dealer 0.000000
2017121521 Live Dealer 0.000000
2017121520 Live Dealer 0.000000
2017121519 Live Dealer 0.000000
2017121518 Live Dealer 0.000000
2017121517 Live Dealer 0.000000
2017121516 Live Dealer 0.000000
2017121515 Live Dealer 0.000000
2017121514 Live Dealer 0.000000
2017121513 Live Dealer 0.000000
2017121512 Live Dealer 0.000000
2017121511 Live Dealer -0.308172
2017121510 Live Dealer 0.000000
2017121509 Live Dealer 0.000000
2017121508 Live Dealer 0.000000
2017121507 Live Dealer -0.154086
2017121506 Live Dealer -0.308172
2017121505 Live Dealer 0.000000
2017121504 Live Dealer 0.000000
2017121503 Live Dealer 0.000000
2017121502 Live Dealer 4.333282
2017121501 Live Dealer 0.000000
2017121500 Live Dealer 0.000000
所以我想要的是例如 TimeId Casino Livedealer
标题中,但我不会在前面知道我将拥有多少种产品类型以及每种产品类型计算NetIncomeByProduct
.
一个例子是:
TimeId Casino Livedealer
----------------------------------
2017121502 4.333282 0.0000
所以我的问题的正确答案是:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(t.ProductType)
FROM #TempTableNetIncomeByProduct t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT TimeId, ' + @cols + ' from
(
select TimeId
, NetIncomeByProduct
, ProductType
from #TempTableNetIncomeByProduct
) x
pivot
(
max(NetIncomeByProduct)
for ProductType in (' + @cols + ')
) p
ORDER BY TimeId'
execute(@query)
我的表格的结果是:
TimeId Casino Live Dealer
2017121500 0.000000 0.000000
2017121501 0.000000 0.000000
2017121502 4.333282 -0.154086
2017121503 0.000000 0.000000
2017121504 0.000000 0.000000
2017121505 0.000000 0.000000
2017121506 0.000000 -0.308172
2017121507 0.000000 -0.154086
2017121508 0.000000 0.000000
2017121509 0.000000 0.000000
2017121510 0.000000 0.000000
2017121511 0.000000 -0.308172
2017121512 0.000000 0.000000
2017121513 0.000000 0.000000
2017121514 0.000000 0.000000
2017121515 0.000000 0.000000
2017121516 0.000000 0.000000
2017121517 0.000000 0.000000
2017121518 0.000000 0.000000
2017121519 0.000000 0.000000
2017121520 0.000000 0.000000
2017121521 0.000000 0.000000
2017121522 0.000000 0.000000
2017121523 0.000000 0.000000