Oracle - Transpose rows into columns

Question

Here is a sample from the table I have been working on:

CREATE TABLE TEST_MC
(
 CLUSTER_K INTEGER,
 PROCESS_PHASE INTEGER,
 SEM_AGG CHAR(1)
);

CLUSTER_K  PROCESS_PHASE   SEM_AGG
==================================
    328          1            M
----------------------------------
    328          2            A
----------------------------------
    328          3            A

Now, I would like to transpose and collapse rows obtaining the following result:

 CLUSTER_K  SEM_AGG_1   SEM_AGG_2   SEM_AGG_3
=============================================
    328         M           A           A

How i can achieve this (Pivot and/or analytical functions)?

Any help is greatly appreciated.

score 2 · Answer 1 · answered May 29 '19 at 10:39

You could do it with a GROUP BY:

select CLUSTER_K,
  max(case when PROCESS_PHASE=1 then SEM_AGG else null end) SEM_AGG_1,
  max(case when PROCESS_PHASE=2 then SEM_AGG else null end) SEM_AGG_2,
  max(case when PROCESS_PHASE=3 then SEM_AGG else null end) SEM_AGG_3
from test_mc
group by CLUSTER_K

score 1 · Answer 2 · answered May 29 '19 at 10:55

if you don't Need dynamic column names you can use Pivot

with tab as(
  select 328 as CLUSTER_K, 1 as process_phase, 'M' as sem_agg from dual union all
  select 328 as CLUSTER_K, 2 as process_phase, 'A' as sem_agg from dual union all
  select 328 as CLUSTER_K, 3 as process_phase, 'A' as sem_agg from dual
)
select  *
from tab 
pivot (max(sem_agg) for process_phase in (1 as sem_agg_1, 2 as sem_agg_2, 3 as sem_agg_3))

db<>fiddle here

Oracle - Transpose rows into columns

2 Answers2

Related