将两个表与一个PIVOT语句结合在一起—SQL Oracle 11g

Question

我有一个这样的表，叫做DWH_DATA:

DATETIME         FK_FEDERAL_STATE     FK_ACCOMODATION   ARRIVALS   NIGHTS
--------------- --------------------  --------------- ----------  ----------
200010               W96-7              61              2433       16111
200011               W96-9              86              3718       30319
200012               W96-3              87              1100        8487
200101               W96-2              998             239         1038

附加信息日期时间=前四个数字=年份+最后两个数字=月份

然后我有一个表格DWH_FEDERAL_STATES:

CODE(PK)         NAME                   
---------- -------------------
W96-2      Country 2
W96-3      Country 3              
W96-9      Country 9    
W96-7      Country 7  

现在我想为每个联邦州安排一整晚的住宿。结果应该是这样的：

Country 2  Country 3  Country 9  Country 7
200010    6979                              16111
200011    ..
200012    ..
200101    ..

我已经尝试过的是PIVOT声明：

SELECT * FROM   
(
SELECT 
DATETIME, 
NIGHTS,
dwh_data.fk_federal_state As federalState
FROM 
dwh_data JOIN DWH_FEDERAL_STATES
ON dwh_data.fk_federal_state = dwh_federal_states.code
) t 
PIVOT(
COUNT(federalState) 
FOR federalState IN ('Country 2','Country 3','Country 9')


) ORDER BY 1;

我得到的结果是：

DATETIME                 NIGHTS 'Country 2' 'Country 3' 'Country 9'
-------------------- ---------- ----------- ----------- -----------
197707                    83648           0           0           0
197707                    87301           0           0           0
197707                    97350           0           0           0

Answer 1

你不想COUNT，你想在晚上SUM，并使用name列：

SELECT datetime,
COALESCE(country_2, 0) AS country_2,
COALESCE(country_3, 0) AS country_3,
COALESCE(country_9, 0) AS country_9
FROM   (
SELECT d.DATETIME,
nights,
f.name
FROM   dwh_data d
JOIN DWH_FEDERAL_STATES f
ON d.fk_federal_state = f.code
)
PIVOT(
SUM(nights) 
FOR name IN (
'Country 2' AS country_2,
'Country 3' AS country_3,
'Country 9' AS country_9
)
)
ORDER BY
datetime;

对于样本数据：

CREATE TABLE dwh_data (DATETIME, FK_FEDERAL_STATE, FK_ACCOMODATION, ARRIVALS, NIGHTS) AS
SELECT 200010, 'W96-7',  61, 2433, 16111 FROM DUAL UNION ALL
SELECT 200011, 'W96-9',  86, 3718, 30319 FROM DUAL UNION ALL
SELECT 200012, 'W96-3',  87, 1100,  8487 FROM DUAL UNION ALL
SELECT 200101, 'W96-2', 998,  239,  1038 FROM DUAL;
CREATE TABLE DWH_FEDERAL_STATES (CODE, NAME) AS
SELECT 'W96-2', 'Country 2' FROM DUAL UNION ALL
SELECT 'W96-3', 'Country 3' FROM DUAL UNION ALL              
SELECT 'W96-9', 'Country 9' FROM DUAL UNION ALL
SELECT 'W96-7', 'Country 7' FROM DUAL;

输出：

COUTRY_90

DATETIME	COUNTRY_2	COUNTRY_3
200010		0	0
200011	0	0		30319
200012	0	8487
200101	1038	0