显示按列名分隔的额外行(有条件地)

Question

我有一个这样的表:

initial   confirmatory  confirmatory1  confirmatory2  confirmatory3
3.4       true          3.6            4.9            7.4
2.1       false         null           null           null

我想生成一个这样的数据库视图:

initial  confirmatory_n  confirmatory_value
3.4      1               3.6
3.4      2               4.9
3.4      3               7.4
2.1      null            2.1

对于confirmatory为假的行，我需要显示第1行时间。对于confirmatory为true的行，我需要在三个不同的行中显示数据3次，并使用额外的列标识显示的值。

我已经搜索了一段时间，但似乎找不到相关的结果。当我试图显示confirmatory_n时，我总是卡住。所以，我没有真正的sql样本，什么是不工作的，因为我觉得我太远了。但是，我确实创建了一个可能有用的sqlfiddle。如果有任何帮助就太好了。

Answer 1

另一个UNPIVOT解决方案:

WITH src ("INITIAL", confirmatory, confirmatory1, confirmatory2, confirmatory3) as (
    SELECT 3.4, 'true', 3.6, 4.9, 7.4 FROM DUAL UNION ALL
    SELECT 2.1, 'false', NULL, NULL, NULL FROM DUAL
), dta as (
select "INITIAL"
     , case upper(confirmatory) when 'FALSE' then "INITIAL" end confirmatory
     , confirmatory1
     , confirmatory2
     , confirmatory3
  from src
)
select *
 from dta
 unpivot (confirmatory_value
     FOR confirmatory_n IN (CONFIRMATORY  AS null,
                            CONFIRMATORY1 AS 1,
                            CONFIRMATORY2 AS 2,
                            CONFIRMATORY3 AS 3));

Answer 2

使用union all:

select initial_value, 1 as confirmatory_n, confirmatory1 as confirmatory
from results t
where confirmatory = 1
union all
select initial_value, 2 as confirmatory_n, confirmatory2 as confirmatory
from results t
where confirmatory = 1
union all
select initial_value, 3 as confirmatory_n, confirmatory3 as confirmatory
from results t
where confirmatory = 1
union all
select initial_value, null as confirmatory_n, initial_value
from results t
where confirmatory = 0;

SQL提琴在这里。如果您的表非常大，并且关注性能，那么还有其他方法只扫描表一次。但是，这种方法通常是足够的。

Answer 3

UNPIVOT解决方案

WITH src ("INITIAL", confirmatory, confirmatory1, confirmatory2, confirmatory3) as (
    SELECT 3.4, 'true', 3.6, 4.9, 7.4 FROM DUAL UNION ALL
    SELECT 2.1, 'false', NULL, NULL, NULL FROM DUAL
)
SELECT
    "INITIAL",
    confirmatory_n,
    confirmatory_value
FROM (
    SELECT
        "INITIAL",
        CASE WHEN confirmatory_value IS NOT NULL THEN confirmatory_n END confirmatory_n,
        CASE WHEN confirmatory_value IS NOT NULL THEN confirmatory_value ELSE "INITIAL" END confirmatory_value
    FROM
        src
        UNPIVOT INCLUDE NULLS (
            confirmatory_value FOR confirmatory_n IN (confirmatory1 AS 1, confirmatory2 AS 2, confirmatory3 AS 3)
        )
    )
GROUP BY
    "INITIAL",
    confirmatory_n,
    confirmatory_value
ORDER BY
    confirmatory_n;

Answer 4

这可能不是最有效的解决方案(特别是对于n的任意值)，但是您可以编写一系列UNION选择来完成此操作。在Oracle 12c中工作。

编辑:编辑到适合你的小提琴

Edit2: UNION ALL性能更高。我的解决方案需要union过滤掉confirmatory为false的重复行。union all解决方案会更高效。

select initial_value,
    case when (confirmatory = 1 and confirmatory1 is not null) then '1' else null end confirmatory_n, 
    case when (confirmatory = 1 and confirmatory1 is not null) then confirmatory1 else initial_value end confirmatory_value
from results
UNION 
select initial_value,
    case when (confirmatory = 1 and confirmatory2 is not null) then '2' else null end confirmatory_n, 
    case when (confirmatory = 1 and confirmatory2 is not null) then confirmatory2 else initial_value end confirmatory_value
from results
UNION 
select initial_value,
    case when (confirmatory = 1 and confirmatory3 is not null) then '3' else null end confirmatory_n, 
    case when (confirmatory = 1 and confirmatory3 is not null) then confirmatory3 else initial_value end confirmatory_value
from results