Oracle 基于重复值的单列到多行

select * from cust;


name   m1      m2       m3
ravi  maruti   honda    toyota
john  honda    toyota   null

ravi maruti
ravi honda
ravi toyota
john honda
john toyota
john null

with t
as (select name,m1,m2,m3
from cust)
select *
from t
unpivot
(value for col in (name,m1,m2,m3));


col value
name ravi
m1   maruti
m2   honda
m3  toyota
name john
m1  honda
m2 toyota

CREATE TABLE t
    (name varchar2(4), m1 varchar2(6), m2 varchar2(6), m3 varchar2(6))
;
    
INSERT INTO t VALUES ('ravi', 'maruti', 'honda', 'toyota');
INSERT INTO t VALUES ('john', 'honda', 'toyota', NULL);

with t1
as (select name,m1,m2,m3 from t)
select *
from t1
unpivot include nulls (
  value for col in (m1,m2,m3)
);

NAME CO VALUE 
---- -- ------
ravi M1 maruti
ravi M2 honda 
ravi M3 toyota
john M1 honda 
john M2 toyota
john M3