Hi All ,
I am looking a help to solve the issue by writing a sql query in Oracle 19c Oracle database.
Requirement :
I want to convert two rows in one row :
-
Unique records for col1,col2,col3 and col4 combination
-
Want to combine two records for col6 into on1 record. You can see the output columns c6_1 and c6_2
-
Same with col5 two records into one record. Output columns C5_1 and C5_2
-
COL5 has diffrent values
-
Col6 has only two posible values either ID1 or ID2
-
Sample date : WITH SAMPLE_DATA AS (
SELECT 1 col1, 111 col2, 'A1' col3, TO_DATE('01-JAN-2020', 'DD-MON-YYYY') col4,'AA' col5,'ID1' col6 FROM DUAL UNION ALL
SELECT 1 col1, 111 col2, 'A1' col3, TO_DATE('01-JAN-2020', 'DD-MON-YYYY') col4,'BB' col5,'ID2' col6 FROM DUAL UNION ALL
SELECT 1 col1, 111 col2, 'A1' col3, TO_DATE('01-JAN-2020', 'DD-MON-YYYY') col4,'AA' col5,'ID1' col6 FROM DUAL UNION ALL
SELECT 1 col1, 111 col2, 'A1' col3, TO_DATE('01-JAN-2020', 'DD-MON-YYYY') col4,'CC' col5,'ID2' col6 FROM DUAL UNION ALL
SELECT 1 col1, 222 col2, 'B1' col3, TO_DATE('01-FEB-2020', 'DD-MON-YYYY') col4,'EE' col5,'ID1' col6 FROM DUAL UNION ALL
SELECT 1 col1, 222 col2, 'B1' col3, TO_DATE('01-FEB-2020', 'DD-MON-YYYY') col4,'FF' col5,'ID2' col6 FROM DUAL UNION ALL
SELECT 1 col1, 111 col2, 'C1' col3, TO_DATE('01-MAR-2020', 'DD-MON-YYYY') col4,'GG' col5,'ID1' col6 FROM DUAL UNION ALL
SELECT 1 col1, 111 col2, 'C1' col3, TO_DATE('01-MAR-2020', 'DD-MON-YYYY') col4,'HH' col5,'ID2' col6 FROM DUAL
) SELECT * from SAMPLE_DATA;