将列转换为具有相同数据的行( SQL )

Question

我有一个名为t1的表，它有6列。现在，我需要创建一个基于列的报告，其中包含C、D、E列中的每个数据，作为表t2这样的单独行。如何在oracle中为相同的SQL查询编写SQL查询。

表T1 (当前表值)

A   B   C   D       E       F
1   AE  21  S-1     F-A     POST
1   AE  31  NULL    NULL    POST
1   AE  41  NULL    NULL    PRE
1   AE  51  S-2     R-A     PRE

表T2 (例外结果)

A   B   C       D       E       F
1   AE  21      NULL    NULL    POST
1   AE  NULL    S-1     NULL    POST
1   AE  NULL    NULL    F-A     POST
1   AE  31      NULL    NULL    POST
1   AE  41      NULL    NULL    PRE
1   AE  NULL    S-2     NULL    PRE
1   AE  NULL    NULL    R-A     PRE
1   AE  51      NULL    NULL    PRE

Answer 1

使用UNION查询分别获取每个字段的值。包括您的键值(a,b)，然后每个非空字段单独。下面是一个例子：

select a,b, c, null d, null e, f from t1 where c is not null
union
select a,b, null c, d, null e, f from t1 where d is not null
union
select a,b, null c, null d, e, f from t1 where e is not null

Answer 2

我做了另一个表t0，并这样使用它：

with t1 as 
        (select 1 A, 'AE' B, 21 C, 'S-1' D, 'F-A' E, 'POST' F from dual union all
         select 1,   'AE',   31,   null ,   null,    'POST' from dual union all
         select 1,   'AE',   41,   null ,   null,    'PRE' from dual union all
         select 1,   'AE',   51,   'S-1' ,  'R-A',   'PRE' from dual),
t0 as 
        (select 'C' x from dual union all
         select 'D' from dual union all
         select 'E' from dual)
select a,
       b,
       case when t0.x = 'C' then c end c,
       case when t0.x = 'D' then d end d,
       case when t0.x = 'E' then e end e,
       f
  from t1,t0 
 where not (    case when t0.x = 'C' then c end is null
            and case when t0.x = 'D' then d end is null
            and case when t0.x = 'E' then e end is null)