I have 8 item codes and description in a single row and need to split them to multiple rows. Could someone please help me with this? If it is just item code, I can use unpivot but we have item code and item desc.
Input table:
PatientID|Modified_Date|Item1_code|Item1_desc|Item2_code|Item2_desc
1|2022-03-27|A11|Amoxicillin|A21|Ampicillin
2|2022-03-28|A21|Ampicillin|A12|Acyclovir
3|2022-03-28|A13|Amikacin|A11|Amoxicillin
Expected Output:
PatientID|Modified_Date|Item_code|Item_desc
1|2022-03-27|A11|Amoxicillin
1|2022-03-27|A21|Ampicillin
2|2022-03-28|A21|Ampicillin
2|2022-03-28|A12|Acyclovir
3|2022-03-28|A13|Amikacin
3|2022-03-28|A11|Amoxicillin
UNPIVOT will do the trick:
with rws as (
select 1 patient_id, date'2022-03-27' dt, 'A11' cd1, 'Amoxicillin' desc1, 'A21' cd2, 'Ampicillin' desc2
from dual union all
select 2 patient_id, date'2022-03-28' dt, 'A21' cd1, 'Ampicillin' desc1, 'A12' cd2, 'Acyclovir' desc2
from dual union all
select 3 patient_id, date'2022-03-28' dt, 'A13' cd1, 'Amikacin' desc1, 'A11' cd2, 'Amoxicillin' desc2
from dual
)
select * from rws
unpivot (
( code, descr ) for source_cols in (
( cd1, desc1 ),
( cd2, desc2 )
)
);
PATIENT_ID DT SOURCE_CO COD DESCR
---------- ----------- --------- --- -----------
1 27-MAR-2022 CD1_DESC1 A11 Amoxicillin
1 27-MAR-2022 CD2_DESC2 A21 Ampicillin
2 28-MAR-2022 CD1_DESC1 A21 Ampicillin
2 28-MAR-2022 CD2_DESC2 A12 Acyclovir
3 28-MAR-2022 CD1_DESC1 A13 Amikacin
3 28-MAR-2022 CD2_DESC2 A11 AmoxicillinI discuss this and the opposite (PIVOTing rows to columns) in detail at:
https://blogs.oracle.com/sql/post/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot#unpivot