我有一组名为BWHourlyReadings
的表格数据,例如:
ServiceID Hour InputOctets OutputOctets
========= ==== =========== =================
27222 1 383088 804249
27222 2 270529 688683
27222 3 247251 290124
... up to 24 hours of data
27222 24 236053 239165
28900 1 883011 914249
28900 3 444251 891124
... up to 24 hours of data
28900 24 123053 452165
每天每个
ServiceID
最多有24个读数。我已经完成了两个独立的
PIVOT
查询,一个用于InputOctets
列,另一个用于OutputOctets
列(为了简洁起见,这里只显示了一个)。-- Replace HourXIn with HourXOut for OutputOctets
SELECT ServiceID, [1] AS 'Hour1In', [2] AS 'Hour2In', [3] AS 'Hour3In', ...
FROM
(
SELECT
ServiceID,
Hour,
TotalInputOctets -- Other query has OutputOctets here instead
FROM
BWHourlyReadings
) AS bw
PIVOT
(
MAX(TotalInputOctets) -- Other query has OutputOctets here instead
FOR [Hour] IN ([1], [2], [3], ... [24])
) AS pvt
这给了我两个单独的结果集,分别是
InputOctets
和OutputOctets
,例如:在
InputOctets
上进行PIVOT
查询的结果:ServiceID Hour1In Hour2In Hour3In . Hour24In
========= ======= ======= ======= ========
27222 383088 270529 247251 236053
28900 883011 0 444251 123053
OutputOctets
的PIVOT
查询结果:
ServiceID Hour1Out Hour2Out Hour3Out .. Hour24Out
========= ======== ======== ======== ========
27222 804249 688683 290124 239165
28900 914249 0 891124 452165
我需要制作一份像这样的报告:
ServiceID Hour1In Hour1Out Hour2In Hour2Out Hour3In Hour3Out .. Hour24In Hour24Out
========= ======= ======== ======= ======== ======= ======== ======= ========
27222 383088 804249 270529 688683 247251 290124 236053 239165
28900 883011 914249 0 0 444251 891124 123053 452165
我如何合并这两个查询结果以生成上面的报告?
更新: 我已经更新了所需报告格式中的数据,以匹配源表示例中的数据。对于混淆造成的不便,我深感抱歉。