SQL - Unpivot/Pivot Rows to Columns

Question

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!

You are trying to pivot, not "unpivot" as you wrote in one place. There is no need for CROSS APPLY - not sure why you though that would be involved. But the biggest problem seems to be that you want to pivot an unknown number of rows (known only after seeing the data) into an unknown number of columns, and that is just not possible in standard SQL. Or do you know beforehand (without seeing the data) that there will always be at most 2 (or at most N, with N a hardcoded constant) such rows per ID? — user5683823
– user5683823, Commented Apr 21, 2022 at 16:05
Thank you for the comment. I adjusted the terminology. At most, the columns will 5 rows per ID. I hope that helps. — Logan
– Logan, Commented Apr 21, 2022 at 16:13

user5683823 · Accepted Answer · 2022-04-21 16:54:32Z

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

D-Shih · Accepted Answer · 2022-04-21 16:28:17Z

1

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

answered Apr 21, 2022 at 16:28

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

2 Comments

Logan Over a year ago

Thank you very much for your assistance! I am running into a slight error. ORA-00923: FROM Keyword not found where expected. I copied the code and not sure why this is occurring. Any thoughts? Please and thank you.

user5683823 Over a year ago

@Logan - the problem is that the column names like Date1 etc were given in single quotes. Those should be double-quotes. Better, don't use mixed capitalization names (anywhere); DATE1 is just as good as Date1. If you must use Date1, it should be in double-quotes. Also, note that DATE is not a valid column name; in my answer I used DATE_ with an underscore. I hope in your real data you have a different name.

Collectives™ on Stack Overflow

SQL - Unpivot/Pivot Rows to Columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related