Oracle : Need to convert ROWS values into COLUMN

Question

I have multiples records which I want to convert into single column.

I have table which contains records like below.

id	prjid	studyid	a_udf1	b_udf1	a_udf2	b_udf2	a_udf3	b_udf3
1	30	2	abc	abc	7-feb-2022	7-feb-2022	chem	chem
2	12	3	xyz	xyz	8-feb-2022	8-feb-2022	polity	null

and I want result as below.

id	prjid	studyid	a_udfvalues	b_udfvalues
1	30	2	abc	abc
1	30	2	7-feb-2022	7-feb-2022
1	30	2	chem	chem
2	12	3	xyz	xyz
2	12	3	8-feb-2022	8-feb-2022
2	12	3	polity	null

and so on.

Oracle Query

SELECT id, prjid,studyid,
       CASE
           WHEN Attribute IN (A_UDF1, A_UDF2, A_UDF3, A_UDF4) THEN 'a_values'
           WHEN Attribute IN (B_UDF1, B_UDF2, B_UDF3) THEN 'b_values'
       END AS Category,
       Value
FROM (
    SELECT id, prjid,studyid, A_UDF1, A_UDF2, A_UDF3, B_UDF1, B_UDF2, B_UDF3
    FROM SAMPLE_T
) t
UNPIVOT (
    Value
    FOR Attribute IN (A_UDF1, A_UDF2, A_UDF3, B_UDF1, B_UDF2, B_UDF3)
);

I was trying above query but not giving correct result. It is giving result as below

id	prjid	studyid	a_udfvalues	b_udfvalues
1	30	2	abc	null
1	30	2	7-feb-2022	null
1	30	2	chem	null
2	12	3	null	xyz
2	12	3	null	8-feb-2022
2	12	3	null	polity

and so on.

I cant filter "NULL" values because sometimes columns may contain null values in that case if i filter null values it will get missing from result. Can somebody please help me with this.

Answer 1

I think what you need is the below:

select id, prj_id, study_id, a_udf1 a_udfvalues, b_udf1 b_udfvalues
from sample_t
union all
select id, prj_id, study_id, a_udf2 a_udfvalues, b_udf2 b_udfvalues
from sample_t
union all
select id, prj_id, study_id, a_udf3 a_udfvalues, b_udf3 b_udfvalues
from sample_t;