Oracle查询将一行转换为两行考虑特定的列

Question

我是Oracle SQL的新手。我需要一个SQL查询，可以转换下面提到的表。我尝试了一些选择，例如Undivot，Union等，但仍然无法实现。

我有一个这样的表：

SNO     Name    Sales   Profit
1      John       50    20
2      Peter      60    NULL 
3      Mark       15    10
4      Nicolas   NULL  -10
5      Alex       70    20

我需要按以下

获得它

SNO     Name    Sales   Profit  Sal/Pro
1      John     50      NULL    Sales
1      John     NULL    20      Profit
2      Peter    60      NULL    Sales
3      Mark     15      NULL    Sales
3      Mark     NULL    10      Profit
4      Nicolas  NULL    -10     Profit
5      Alex     70      NULL    Sales
5      Alex     NULL    20      Profit

另外，如果还有其他方法可以处理此方法。

Answer 1

一种简单的方法使用union all：

select SNO, Name, Sales, NULL as Profit, 'Sales' as sales_pro
from t
where profit is not null
union all
select SNO, Name, NULL as Sales, Profit, 'Profit' as sales_pro
from t
where sales is not null;

Answer 2

select
    SNO, Name, Sales, Profit,
    case
        when Sales is not null then 'Sales'
        when Profit is not null then 'Profit'
        else null
    end as sales_pro
from t

Answer 3

有效的方法(仅从基本表中读取数据一次(使用交叉加入到一个小助手表上，您可以在飞行上创建 - 作为内联视图，在来自查询的条款。

请注意顶部的带有子句 - 它只是允许我测试查询。它不是解决方案的一部分；删除它，并使用您的实际表格和列名。

with
  test_data ( sno, name, sales, profit ) as (
    select 1, 'John'   ,   50,   20 from dual union all
    select 2, 'Peter'  ,   60, NULL from dual union all 
    select 3, 'Mark'   ,   15,   10 from dual union all
    select 4, 'Nicolas', NULL,  -10 from dual union all
    select 5, 'Alex'   ,   70,   20 from dual
  )
select   sno, name, 
         case sal_pro when 'Sales'  then sales  end as sales,
         case sal_pro when 'Profit' then profit end as profit, sal_pro
from     test_data
         cross join
         (select 'Sales' as sal_pro from dual union all select 'Profit' from dual)
where    case sal_pro when 'Sales' then sales else profit end is not null
order by sno, sales
;
       SNO NAME         SALES     PROFIT SAL_PR
---------- ------- ---------- ---------- ------
         1 John            50            Sales 
         1 John                       20 Profit
         2 Peter           60            Sales 
         3 Mark            15            Sales 
         3 Mark                       10 Profit
         4 Nicolas                   -10 Profit
         5 Alex            70            Sales 
         5 Alex                       20 Profit