Pivoting dynamic columns

Question

I trying to pivot a table creating the columns dynamically according to the rows.

I'm using the clause PIVOT XML, but I'm not getting parse the XML column to columns.

My table looks like this:

I would like this:

Could you provide text description of the table? – Sayan Malakshinov Aug 24 '20 at 20:37 — Sayan Malakshinov, Aug 24 '20 at 20:37

score 0 · Answer 1 · answered Aug 24 '20 at 21:34

Pivot xml returns XMLType, but you showed standard table structure. Example of pivot xml:

with t(id, dt, medical_test,positive) as (
select 1, date'2020-04-20','blood count'                       , 1 from dual union all
select 2, date'2020-04-26','bone marrow aspiration'            , 1 from dual union all
select 2, date'2020-04-30','cephalin-cholesterol flocculation' , 0 from dual union all
select 2, date'2020-05-05','hematocrit'                        , 0 from dual union all
select 3, date'2020-04-02','lumbar puncture'                   , 0 from dual union all
select 3, date'2020-04-13','Pap smear'                         , 1 from dual union all
select 4, date'2020-06-06','cephalin-cholesterol flocculation' , 1 from dual
)
select *
from t
pivot xml(
   max(positive) as positive
   for medical_test in (any)
);

Result:

        ID DT                  MEDICAL_TEST_XML
---------- ------------------- ------------------------------------------------------------------------------------
         1 2020-04-20 00:00:00 <PivotSet><item><column name = "MEDICAL_TEST">blood count</column><column name = "POSITIVE">1</column></item></PivotSet>
         2 2020-04-26 00:00:00 <PivotSet><item><column name = "MEDICAL_TEST">bone marrow aspiration</column><column name = "POSITIVE">1</column></item></PivotSet>
         2 2020-04-30 00:00:00 <PivotSet><item><column name = "MEDICAL_TEST">cephalin-cholesterol flocculation</column><column name = "POSITIVE">0</column></item></PivotSet>
         2 2020-05-05 00:00:00 <PivotSet><item><column name = "MEDICAL_TEST">hematocrit</column><column name = "POSITIVE">0</column></item></PivotSet>
         3 2020-04-02 00:00:00 <PivotSet><item><column name = "MEDICAL_TEST">lumbar puncture</column><column name = "POSITIVE">0</column></item></PivotSet>
         3 2020-04-13 00:00:00 <PivotSet><item><column name = "MEDICAL_TEST">Pap smear</column><column name = "POSITIVE">1</column></item></PivotSet>
         4 2020-06-06 00:00:00 <PivotSet><item><column name = "MEDICAL_TEST">cephalin-cholesterol flocculation</column><column name = "POSITIVE">1</column></item></PivotSet>

It's easier to achieve what you want with simple PIVOT:

with t(id, dt, medical_test,positive) as (
select 1, date'2020-04-20','blood count'                       , 1 from dual union all
select 2, date'2020-04-26','bone marrow aspiration'            , 1 from dual union all
select 2, date'2020-04-30','cephalin-cholesterol flocculation' , 0 from dual union all
select 2, date'2020-05-05','hematocrit'                        , 0 from dual union all
select 3, date'2020-04-02','lumbar puncture'                   , 0 from dual union all
select 3, date'2020-04-13','Pap smear'                         , 1 from dual union all
select 4, date'2020-06-06','cephalin-cholesterol flocculation' , 1 from dual
)
select--+ no_merge
  piv.*
from t
pivot (
   max(positive)
   for medical_test in (
      'blood count'                       ,
      'bone marrow aspiration'            ,
      'cephalin-cholesterol flocculation' ,
      'hematocrit'                        ,
      'lumbar puncture'                   ,
      'Pap smear'                         
      )
) piv
order by 1,2;

Results:

        ID DT                  'blood count' 'bone marrow aspiration' 'cephalin-cholesterol flocculation' 'hematocrit' 'lumbar puncture' 'Pap smear'
---------- ------------------- ------------- ------------------------ ----------------------------------- ------------ ----------------- -----------
         1 2020-04-20 00:00:00             1
         2 2020-04-26 00:00:00                                      1
         2 2020-04-30 00:00:00                                                                          0
         2 2020-05-05 00:00:00                                                                                       0
         3 2020-04-02 00:00:00                                                                                                         0
         3 2020-04-13 00:00:00                                                                                                                     1
         4 2020-06-06 00:00:00                                                                          1

Pivoting dynamic columns

1 Answers1