有沒有辦法創建一個資料透視表，根據一些 if 條件（從平面表到胖表）將單列轉換為多列？
CREATE TABLE table_name (A,B,C,D) AS
SELECT 'A', '1', '4', DATE '2000-01-04' FROM DUAL UNION ALL
SELECT 'A', '1', '6', DATE '2000-01-04' FROM DUAL UNION ALL
SELECT 'A', '2', '1', DATE '2000-01-04' FROM DUAL UNION ALL
SELECT 'B', '1', '20', DATE '2000-01-04' FROM DUAL UNION ALL
SELECT 'B', '2', '2', DATE '2000-01-04' FROM DUAL UNION ALL
SELECT 'B', '-3', '999', DATE '2000-01-04' FROM DUAL UNION ALL
SELECT 'A', '1', '30', DATE '2000-01-05' FROM DUAL UNION ALL
SELECT 'B', '2', '3', DATE '2001-01-05' FROM DUAL;
每列 A 和 D 的預期結果
A, D, C where B == 1, C where B == 2
A, 2000-01-04, 10, 1
B, 2000-01-04, 20, 2
A, 2000-01-05, 30, 0
B, 2000-01-05, 0, 3
或每列 D
D, C where B == 1 - C where B == 2
2000-01-04, 27
2000-01-05, 27
uj5u.com熱心網友回復：
你可以做：
select
a,
d,
max(case when b = 1 then c end) as b1,
max(case when b = 2 then c end) as b2
from table_name
group by a, d
和：
select
d,
max(case when b = 1 then c end) -
max(case when b = 2 then c end) as x
from table_name
group by d
請參閱db<>fiddle的運行示例。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/531647.html
上一篇：Pythonoracledb連續查詢通知未從資料庫發送訊息
下一篇：各部門平均工資