SQL -反透视/透视行到列

Question

使用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将非常感激-请和感谢!

Answer 1

像这样:

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    

Answer 2

你可能想使用pivot而不是unpivot。

您可以尝试使用条件聚合函数使pivot

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