hi
i have a sample scenario,i have a table called cust which is like below
select * from cust;
name m1 m2 m3
ravi maruti honda toyota
john honda toyota null
i want the output like below
ravi maruti
ravi honda
ravi toyota
john honda
john toyota
john null
this is what i did using unpivot option ,and this is what i got
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
i tried in different ways but could not the required format.plz help me...
regards
arun
You're only unpivoting columns M1, M2 & M3. So name shouldn't be in the unpivot clause! And if you want nulls to appear you need to use the "include nulls" clause:
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
You can read more about unpivoting at:
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot And please, in future provide your test cases in the form of "create table + inserts"!