根据条件将一行分解为多行

Question

我有一个这样的表：

ID    A_Kode    B_Kode    C_Kode
--------------------------------
1     10        12        0
2     15        0         0
3     0         16        17
4     0         0         0

需要提供以下结果的查询：

ID    Kode 
------------
1     10
1     12
2     15
3     16
3     17
4     0

Answer 1

也许union all对你有好处？

select ID, A_Kode
from tab
union all
select ID, B_Kode
from tab
where B_Kode <> 0
union all
select ID, C_Kode
from tab
where C_Kode <> 0
order by ID

Answer 2

首先需要UNION所有存在非零值的行，然后添加具有0的行，其中所有列的值都为零。

select id,A_Kode as Kode from t where A_Kode<>0
union all
select id,B_Kode as Kode from t where B_Kode<>0
union all
select id,C_Kode as Kode from t where C_Kode<>0
union all
select id,0 as Kode from t where A_Kode=0 and B_Kode=0 and C_Kode=0