使用 PL/SQL Developer，我試圖基本上將一些行轉換為列，但這樣做很困難。我認為解決方案是使用交叉應用然后取消透視，但也許我以錯誤的方式接近這個。
編輯：從一個非常有用的評論中，一個 ID 最多將有 5 行與之關聯。
輸入表：
ID Date Location
111 1/05/2020 Cafe1
222 1/02/2020 Park1
222 1/11/2020 Cafe2
輸出表：
ID Date1 Location1 Date2 Location2
111 1/05/2020 Cafe1
222 1/02/2020 Park1 1/11/2020 Cafe2
代碼嘗試：
SELECT *
FROM (
SELECT A.ID
,B.*
FROM MYTABLE as A
CROSS APPLY (Date, Location)
) B(Item,Value)
) src
PIVOT (max(value) for Item in ([Date], [Location])
不幸的是，我遇到了一些令人討厭的錯誤。任何對 unpivot 的幫助/指導將不勝感激 - 請感謝您！
uj5u.com熱心網友回復：
像這樣的東西：
with
test_data (id, date_, location) as (
select 111, to_date('1/05/2020', 'mm/dd/yyyy'), 'Cafe1' from dual union all
select 222, to_date('1/02/2020', 'mm/dd/yyyy'), 'Park1' from dual union all
select 222, to_date('1/11/2020', 'mm/dd/yyyy'), 'Cafe2' from dual
)
-- end of test data (for illustration only); REMOVE the code above, and use your
-- actual table and column names below
select id, "1_DT" as date1, "1_LOC" as location1,
"2_DT" as date2, "2_LOC" as location2,
"3_DT" as date3, "3_LOC" as location3
from ( select t.*, row_number() over (partition by id order by date_) as rn
from test_data t
)
pivot (min(date_) as dt, min(location) as loc for rn in (1, 2, 3))
;
ID DATE1 LOCATION1 DATE2 LOCATION2 DATE3 LOCATION3
---- ---------- ---------- ---------- ---------- ---------- ----------
111 01/05/2020 Cafe1
222 01/02/2020 Park1 01/11/2020 Cafe2
uj5u.com熱心網友回復：
您可能想要獲得支點而不是取消支點。
您可以嘗試使用條件聚合函式進行樞軸
SELECT ID,
MAX(CASE WHEN rn = 1 THEN Date END) 'Date1',
MAX(CASE WHEN rn = 1 THEN Location END) 'Location1',
MAX(CASE WHEN rn = 2 THEN Date END) 'Date2',
MAX(CASE WHEN rn = 2 THEN Location END) 'Location2'
FROM (
SELECT t1.*,
ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date) rn
FROM MYTABLE t1
) t1
GROUP BY ID
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/461900.html