我有下面这样的表，其中有类似的数据
| cust | val1 | val2 | val3 | val4 | val5 |
---------------------------------------------------
| 1 | x1 | | x3 | | |
| 2 | | | | x4 | |
| 3 | x1 | | | | |
| 4 | x1 | x2 | x3 | x4 | x5 |
我希望输出为
cust val1 val2 val3 val4 val5
1 x1 x3
2 x4
3 x1
4 x1 x2 x3 x4 x5
create table test_nth_nonull (cust varchar2(3),val1 varchar2(3),val2 varchar2(3),val3 varchar2(3), val4 varchar2(3), val5 varchar2(3));
insert into test_nth_nonull values(1,'x1',null,'x3',null,null);
insert into test_nth_nonull values(2,null,null,null,'x4',null);
insert into test_nth_nonull values(3,'x1',null,null,null,null);
insert into test_nth_nonull values(4,'x1','x2','x3','x4','x5');
select* from test_nth_nonull;
请在查询中帮助实现这一点
您可以使用pivot和unpivot子句。首先unpivot列到行以获得行号(该子句默认忽略null(，然后pivot"；摇下来"；值返回行到它们的新位置：
with test_nth_nonull (cust,val1,val2,val3,val4,val5) as (
select 1,'x1',null,'x3',null,null from dual union all
select 2,null,null,null,'x4',null from dual union all
select 3,'x1',null,null,null,null from dual union all
select 4,'x1','x2','x3','x4','x5' from dual
), unpivotted as (
select *
from test_nth_nonull
unpivot (val for col in (val1 as 1, val2 as 2, val3 as 3, val4 as 4, val5 as 5))
), numbered as (
select u.cust, row_number() over (partition by u.cust order by u.col) as rn, u.val
from unpivotted u
)
select *
from numbered
pivot (max(val) for rn in (1 as val1, 2 as val2, 3 as val3, 4 as val4, 5 as val5))
Db小提琴在这里。
这里有一个选项。
这就是你所拥有的：
SQL> select* from test_nth_nonull order by cust;
CUST VAL1 VAL2 VAL3 VAL4 VAL5
------ ----- ----- ----- ----- -----
1 x1 x3
2 x4
3 x1
4 x1 x2 x3 x4 x5
这就是你想要的(阅读代码中的注释(：
SQL> with
2 temp as
3 -- concatenate all values
4 (select cust, val1 ||';'|| val2||';'|| val3||';'|| val4||';'|| val5 val
5 from test_nth_nonull
6 ),
7 temp2 as
8 -- split concatenated column to rows
9 (select cust,
10 regexp_substr(val, '[^;]+', 1, column_value) val,
11 column_value cv
12 from temp
13 cross join table(cast(multiset(select level from dual
14 connect by level <= 5
15 ) as sys.odcinumberlist))
16 )
17 -- final result
18 select cust,
19 max(case when cv = 1 then val end) v1,
20 max(case when cv = 2 then val end) v2,
21 max(case when cv = 3 then val end) v3,
22 max(case when cv = 4 then val end) v4,
23 max(case when cv = 5 then val end) v5
24 from temp2
25 group by cust
26 order by cust;
CUST V1 V2 V3 V4 V5
------ ---- ---- ---- ---- ----
1 x1 x3
2 x4
3 x1
4 x1 x2 x3 x4 x5
SQL>
将nvl((和decode((与cte一起使用将得到您想要的。如果val1为空，nvl(val1，val2(将返回val2，否则将返回val1。decode(val2，val1，null，val2(表示如果val2=val1，则为null，否则为val2
with cte as (
select cust , nvl(nvl(nvl(nvl(val1 ,val2) ,val3 ), val4 ), val5 )val1,nvl(nvl(nvl(val2 ,val3 ), val4 ), val5 )val2,nvl(nvl(val3, val4 ), val5 )val3,nvl( val4 , val5 )val4,val5
from test_nth_nonull
)
select cust,val1,decode(val2,val1,null,val2)val2,decode(val3,val2,null,val1,null,val3)val3,decode(val4,val3,null,val2,null,val1,null,val4)val4,decode(val5,val4,null,val3,null,val2,null,val1,null,val5) val5 from cte
我将使用横向连接来取消数据透视：
select t.*, x.*
from test_nth_nonull t cross join lateral
(select max(case when seqnum = 1 then val end) as val_1,
max(case when seqnum = 2 then val end) as val_2,
max(case when seqnum = 3 then val end) as val_3,
max(case when seqnum = 4 then val end) as val_4,
max(case when seqnum = 5 then val end) as val_5
from (select val, row_number() over (order by n) as seqnum
from (select 1 as n, t.val1 as val from dual union all
select 2, t.val2 as val from dual union all
select 3, t.val3 as val from dual union all
select 4, t.val4 as val from dual union all
select 5, t.val5 as val from dual
) x
where val is not null
) x
) x;
与将值放入字符串并处理字符串相比，此版本的优势在于，它保留了列的原始数据类型。
与pivot/unpivot相比，在整个表中这样做的好处应该是性能。枢转仅在每一行内，这应该更快。