Oracle Pivot SQL

Question

I would like two ID's to be pivoted into the same row based on matching/like PAIR field values. Currently the ID's are just listed in a single column and I am ordering by the PAIR field value.

Below is the query that I am currently running.

select ID,PAIR
from   Table
where   PAIR in
 (select PAIR from Table  
  where ID  in
   (select ID
   from review
   where rule = 1234456
   and trunc(RECVDDATE) = trunc(sysdate-1) 
   )
 )
order by PAIR;

        ID PAIR
   4173910 1175
   4423979 1175
   4425330 17B5
   4106895 17B5
   4421153 E54E
   3706124 E54E

6 rows selected.

I would like this as my desired result. Basically pivoting the ID based on like/matching PAIR values.

     ID(1)   ID(2)
   4173910 4423979
   4425330 4106895
   4421153 3706124

You don't need the value of PAIR in the result? Then: How do you decide which ID to show as ID(1) and which as ID(2)? Also note that column names with parentheses in them are not a good idea; how about ID_1 and ID_2, for example? — user5683823
– user5683823, Commented Jun 20, 2019 at 14:14
Also: For any given PAIR, do you know for sure you will get at most two different ID's? If not (if for a PAIR you may get three or more ID's), which two ID's do you want to show in the "desired result"? — user5683823
– user5683823, Commented Jun 20, 2019 at 14:16
Thanks for your comment. I could also display PAIR in the result, but its not necessary. — salazar44
– salazar44, Commented Jun 20, 2019 at 14:22
ID_1 and ID_2 are a better option. Thanks for pointing that out. — salazar44
– salazar44, Commented Jun 20, 2019 at 14:23

score 1 · Accepted Answer · 2019-06-20 14:38:03Z

1

Depending on your exact requirement (see my Comments below your question), this may work:

(1) Add GROUP BY PAIR right before ORDER BY PAIR.

(2) Change the SELECT clause to

select min(id) as id_1, case when max(id) != min(id) then max(id) end as id_2

The CASE expression is there in case there is only one ID for some PAIR values - in that case ID_2 should not repeat the value from ID_1, but should be NULL instead. (That's how PIVOT would work, anyway.)

Note that this solution doesn't use the PIVOT operator; given what you have already, adding aggregation is simpler. PIVOT would require making your current query into a subquery and then pivoting in an outer SELECT statement.

If you prefer, for brevity you could use NULLIF(MAX(ID), MIN(ID)) instead of the CASE expression; it means exactly the same thing.

edited Jun 20, 2019 at 14:38

answered Jun 20, 2019 at 14:27

user5683823

Sign up to request clarification or add additional context in comments.

2 Comments

salazar44 Over a year ago

Thats awesome! Thank you so much. I went with the case option.

salazar44 Over a year ago

I have a follow up. I dont want to post another question. How would I also return the REVIEW / RECVDDATE value?

Collectives™ on Stack Overflow

Oracle Pivot SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related