sql - oracle sql中如何将列转换为行

Question

我有一个包含以下数据的表格，这里总共有 8 行样本为 3 行 -

现在我使用下面的查询将使用 case 语句的查询转换为这个 -

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);

现在我想让 o/p 在一行中删除所有空值。基本上我想创建一个游标并将要在过程中使用的表的值作为 p_main_prd 或 p_sec_prd 或其余表作为要求传递。

标签： sqloracle

Answer 1

你快到了，你只需要聚合：

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;

或使用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
  )
)

其中，对于样本数据：

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

输出：

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<>在这里摆弄