Skip to Main Content
  • Questions
  • single column to multi rows based on repetition value

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, arun.

Asked: July 08, 2017 - 6:07 pm UTC

Last updated: July 10, 2017 - 9:23 am UTC

Version: 11

Viewed 1000+ times

You Asked

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

and Chris said...

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"!

Is this answer out of date? If it is, please let us know via a Comment