How to transform columns to rows in oracle sql

Question

I have a table having below data , total 8 rows here sample for 3 rows -

now I transformed the query using case statement to this using below query -

select  
case when entity ='PRODUCT' then prd_table_main end P_main_prd ,
case when entity ='PRODUCT' then prd_table_sec end P_sec_prd,
case when entity ='CUSTOMER' then cus_table_main end P_main_cus ,
case when entity ='CUSTOMER' then cus_table_sec end p_sec_cus,
case when entity  ='PROFIT' then prof_table_main end p_main_prof ,
case when entity  ='PROFIT' then prof_table_sec end p_sec_prof
from (
select * from above table);

Now I want to have the o/p as in one row removing all the nulls. Basically I want to create a cursor and pass the value of tables to be used in the procedure as p_main_prd or p_sec_prd or the remaining ones as the requirement.

You are transposing rows to columns (not columns to rows) as you want to reduce the number of rows and increase the number of columns. — MT0
– MT0, Commented Apr 26, 2021 at 9:21
I'm lost. You have column names named table1 and table2. Your query references values in columns, not column names. What does the data really look like? How many tables do you really have? — Gordon Linoff
– Gordon Linoff, Commented Apr 26, 2021 at 11:17

MT0 · Accepted Answer · 2021-04-26 09:15:48Z

2

You are almost there, you just need to aggeregate:

select MAX( case when entity = 'product'  then table1 end ) AS P_main_prd,
       MAX( case when entity = 'product'  then table2 end ) AS P_sec_prd,
       MAX( case when entity = 'customer' then table1 end ) AS P_main_cus,
       MAX( case when entity = 'customer' then table2 end ) AS p_sec_cus,
       MAX( case when entity = 'profit'   then table1 end ) AS p_main_prof,
       MAX( case when entity = 'profit'   then table2 end ) AS p_sec_prof
from   table_name;

or use PIVOT:

SELECT prd_p_main AS p_main_prd,
       prd_p_sec AS p_sec_prd,
       cus_p_main AS p_main_cus,
       cus_p_sec AS p_sec_cus,
       prof_p_main AS p_main_prof,
       prof_p_sec AS p_sec_prof
FROM   table_name
PIVOT (
  MAX( table1 ) AS p_main,
  MAX( table2 ) AS p_sec
  FOR entity IN (
    'product' AS prd,
    'customer' AS cus,
    'profit' AS prof
  )
)

Which, for the sample data:

CREATE TABLE table_name ( entity, table1, table2 ) AS
SELECT 'product', 'prd_table_main', 'prd_table_sec' FROM DUAL UNION ALL
SELECT 'customer', 'cus_table_main', 'cus_table_sec' FROM DUAL UNION ALL
SELECT 'profit', 'prof_table_main', 'prof_table_sec' FROM DUAL

Outputs:

P_MAIN_PRD P_SEC_PRD P_MAIN_CUS P_SEC_CUS P_MAIN_PROF P_SEC_PROF

prd_table_main prd_table_sec cus_table_main cus_table_sec prof_table_main prof_table_sec

db<>fiddle here

answered Apr 26, 2021 at 9:15

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

neha Over a year ago

Thanks I need this exactly ~ :)

Isi · Accepted Answer · 2021-04-26 08:14:20Z

0

Maybe you can try UNPIVOT. I'm not an expert on this, so I can only help you by giving you directions. Check this out, probably this will help you out, or at least show you another way: Oracle Unpivot

answered Apr 26, 2021 at 8:14

Isi

1231 gold badge2 silver badges14 bronze badges

1 Comment

neha Over a year ago

I want to have output in single row removing nulls

Collectives™ on Stack Overflow

How to transform columns to rows in oracle sql

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related