當A列和B列中存在非空值時，我正在尋找將一行中的值分成兩部分的有效方法
uj5u.com熱心網友回復：
你可以UNPIVOT：
SELECT output, col3
FROM table_name
UNPIVOT (
output FOR key IN (cola, colb)
)
或者，替換table_name為()包含您的查詢的括號。其中，對于樣本資料：
其中，對于樣本資料：
CREATE TABLE table_name (cola, colb, col3) AS
SELECT LEVEL, CASE LEVEL WHEN 2 THEN 6 END, CHR(64 LEVEL) || CHR(64 LEVEL)
FROM DUAL
CONNECT BY LEVEL <= 5;
輸出：
輸出 COL3 1 AA 2 BB 6 BB 3 抄送 4 DD 5 EE
db<>在這里擺弄
uj5u.com熱心網友回復：
這是一種選擇；合并兩個集合，只回傳那些cola不為空的。
樣本資料：
SQL> with test (cola, colb, colc) as
2 (select 1, null, 'AA' from dual union all
3 select 2, 6, 'BB' from dual union all
4 select 3, null, 'CC' from dual union all
5 select 4, null, 'DD' from dual union all
6 select 5, null, 'EE' from dual
7 ),
查詢從這里開始：
8 temp as
9 (select cola, colc from test
10 union all
11 select colb, colc from test
12 )
13 select cola, colc
14 from temp
15 where cola is not null
16 order by colc, cola;
COLA COLC
---------- ----------
1 AA
2 BB
6 BB
3 CC
4 DD
5 EE
6 rows selected.
SQL>
您評論說“輸入”是許多連接和嵌套查詢的結果；沒問題 - 只需將那個復雜的查詢用作 CTE：
with test as
(your complicated query with joins and nested queries goes here),
temp as
the rest is copy/paste from above
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/439856.html