SQL - Unpivot/Pivot Rows to Columns-CodePudding

Using PL/SQL Developer, I am attempting to basically unpivot some rows into columns, but am having a difficult time doing so. I think the solution would be to use a cross apply and then unpivot, but maybe I am approaching this the wrong way.

EDIT: From a very helpful comment, at most, an ID will have 5 rows to associated to it.

Input Table:

ID   Date       Location
111  1/05/2020  Cafe1
222  1/02/2020  Park1
222  1/11/2020  Cafe2

Output Table:

ID   Date1      Location1  Date2      Location2
111  1/05/2020  Cafe1   
222  1/02/2020  Park1      1/11/2020  Cafe2

Code Attempt:

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])

I am running into some nasty errors, unfortunately. Any help/guidance to unpivot would be greatly appreciated - please and thank you!

CodePudding user response：

Something like this:

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

CodePudding user response：

You maybe might want to get pivot instead of unpivot.

You can try to use the condition aggregate function to make 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