我正在嘗試將行轉換為列而沒有任何運氣。我現在擁有的
| WEEK_NUMBER | VAL9 | VAL4 | VAL5 | VAL7 |
|---|---|---|---|---|
| 45 | 63.27% | 28.58% | 7.92% | 0.23% |
| 52 | 65.66% | 26.16% | 7.80% | 0.38% |
| 50 | 90.97% | 8.45% | 0.29% | 0.29% |
| 46 | 82.49% | 12.43% | 3.01% | 2.06% |
| 47 | 66.33% | 29.53% | 3.77% | 0.36% |
| 51 | 65.36% | 31.83% | 2.69% | 0.11% |
| 49 | 61.88% | 28.29% | 9.46% | 0.37% |
| 44 | 45.34% | 45.55% | 8.45% | 0.66% |
| 48 | 43.75% | 40.01% | 16.01% | 0.23% |
這是基于以下查詢
select
to_char(for_date,'ww') as week_number
,val9
,val4
,val5
,val7
from data_tab
where to_char(for_date,'yy') = '21'
我正在嘗試獲得以下結果
| 01 | 02 | .. | 51 | 52 | |
|---|---|---|---|---|---|
| VAL9 | 0 | 0 | .. | 65.36% | 65.66% |
| VAL4 | 0 | 0 | .. | 31.83% | 26.16% |
| VAL5 | 0 | 0 | .. | 2.69% | 7.80% |
| VAL7 | 0 | 0 | .. | 0.11% | 0.38% |
我試過做下一個查詢，但沒有運氣。如果有人能指出我正確的方向，我將不勝感激。
select * from (
select
to_char(for_date,'ww') as week_number
,val9
,val4
,val5
,val7
from data_tab
where to_char(for_date,'yy') = '21')
pivot (
min(val9)--, min(val4), min(val5), min(val7)
for week_number in (01 ,02 ,03 ,04 ,05 ,06 ,07 ,08 ,09 ,10 ,11 ,12 ,13 ,14 ,15 ,16 ,17 ,18 ,19 ,20 ,21 ,22 ,23 ,24 ,25 ,26 ,27 ,28 ,29 ,30 ,31 ,32 ,33 ,34 ,35 ,36 ,37 ,38 ,39 ,40 ,41 ,42 ,43 ,44 ,45 ,46 ,47 ,48 ,49 ,50 ,51 ,52 ,53)
)
uj5u.com熱心網友回復：
UNPIVOT然后PIVOT：
SELECT *
FROM (
SELECT to_char(for_date,'ww') as week_number
, val9
, val4
, val5
, val7
FROM data_tab
WHERE for_date >= DATE '2021-01-01'
AND for_date < DATE '2022-01-01'
-- WHERE EXTRACT(YEAR FROM for_date) = 2021
)
UNPIVOT (
value FOR key IN (val9, val4, val5, val7)
)
PIVOT (
MAX(value) FOR week_number IN (
'01' AS "01",
'02' AS "02",
-- ...
'44' AS "44",
'45' AS "45",
'46' AS "46",
'47' AS "47",
'48' AS "48",
'49' AS "49",
'50' AS "50",
'51' AS "51",
'52' AS "52",
'53' AS "53"
)
)
其中，對于樣本資料：
CREATE TABLE data_tab (for_date, val9, val4, val5, val7) AS
SELECT DATE '2021-11-13', 63.27, 28.58, 7.92, 0.23 FROM DUAL UNION ALL
SELECT DATE '2021-12-31', 65.66, 26.16, 7.80, 0.38 FROM DUAL UNION ALL
SELECT DATE '2021-12-17', 90.97, 8.45, 0.29, 0.29 FROM DUAL UNION ALL
SELECT DATE '2021-11-20', 82.49, 12.43, 3.01, 2.06 FROM DUAL UNION ALL
SELECT DATE '2021-11-27', 66.33, 29.53, 3.77, 0.36 FROM DUAL UNION ALL
SELECT DATE '2021-12-24', 65.36, 31.83, 2.69, 0.11 FROM DUAL UNION ALL
SELECT DATE '2021-12-10', 61.88, 28.29, 9.46, 0.37 FROM DUAL UNION ALL
SELECT DATE '2021-11-06', 45.34, 45.55, 8.45, 0.66 FROM DUAL UNION ALL
SELECT DATE '2021-12-03', 43.75, 40.01, 16.01, 0.23 FROM DUAL;
輸出：
鑰匙 01 02 44 45 46 47 48 49 50 51 52 53 VAL9 空值 空值 空值 45.34 63.27 82.49 66.33 43.75 61.88 90.97 65.36 65.66 VAL4 空值 空值 空值 45.55 28.58 12.43 29.53 40.01 28.29 8.45 31.83 26.16 VAL5 空值 空值 空值 8.45 7.92 3.01 3.77 16.01 9.46 .29 2.69 7.8 VAL7 空值 空值 空值 .66 .23 2.06 .36 .23 .37 .29 .11 .38
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/407688.html
標籤：