Converting group of values to one line by either pivot or matrix by group

Question

I am somewhat stumped in that I am familiar with pivot- I am looking for away to take already aggregated values and put them into one line based on ID and medication type- for example: T0 is the total dose for the first day, T5 is total dose for the 5th day, Total_dose is the total dose for all days

The input:

with samp as (select 1 as ID, 'A' as medication, 6 as T0, 8 as T5, 35 as total_dose from dual union all
         select 1 as ID, 'B' as medication, 3 as T0, 2 as T5, 15 as total_dose from dual union all
         select 2 as ID, 'A' as medication, 6 as T0, NULL as T5, 18 as total_dose from dual union all
         select 2 as ID, 'C' as medication, 100 as T0, 120 as T5, 550 as total_dose from dual)
         
select * from samp;

What it is

ID	Medication	T0	T5	Total_dose
1	A	6	8	35
1	B	3	2	15
2	A	6		18
2	C	100	120	550

What I want is:

ID	T0_A	T5_A	Total_dose_A	T0_B	T5_B	Total_dose_B	T0_C	T5_C	Total_dose_C
1	6	8	35	3	2	15
2	6		18				100	120	550

I need to do this dynamically speaking as there may some 100 medications. Any assistance appreciated.

Answer 1

You can use:

WITH samp (id, medication, T0, T5, total_dose) AS (
  SELECT 1, 'A',   6,    8,  35 FROM DUAL UNION ALL
  SELECT 1, 'B',   3,    2,  15 FROM DUAL UNION ALL
  SELECT 2, 'A',   6, NULL,  85 FROM DUAL UNION ALL
  SELECT 2, 'C', 100,  120, 550 FROM DUAL
)
SELECT id,
       a_t0 AS t0_a,
       a_t5 AS t5_a,
       a_total_dose AS total_dose_a,
       b_t0 AS t0_b,
       b_t5 AS t5_b,
       b_total_dose AS total_dose_b,
       c_t0 AS t0_c,
       c_t5 AS t5_c,
       c_total_dose AS total_dose_c
FROM   samp
PIVOT (
  MAX(T0) AS t0,
  MAX(T5) AS t5,
  MAX(Total_Dose) AS total_dose
  FOR medication IN (
    'A' AS a,
    'B' AS b,
    'C' AS c
  )
);

Which outputs:

ID	T0_A	T5_A	TOTAL_DOSE_A	T0_B	T5_B	TOTAL_DOSE_B	T0_C	T5_C	TOTAL_DOSE_C
1	6	8	35	3	2	15	null	null	null
2	6	null	85	null	null	null	100	120	550

If you want the columns as A_T0, A_T5, A_TOTAL_DOSE, etc. then you can just use SELECT * rather than renaming the columns.

fiddle

I need to do this dynamically speaking as there may some 100 medications.

Oracle does not natively support dynamic pivots; just type all the 100 values.

If you really want a dynamic pivot then you are going to have to write some PL/SQL code to generate the query.