Oracle 11 SQL : Is there a way to split 1 row into x rows

Question

Customer asked to Split 1 row from the Oracle DB SQL into 6 rows.

Let's say, originally the SQL (complex sql with multiple joins , etc) is pulling in 9 columns:
select A, B, C, D, E, F, G, H, I from X, Y, Z . . . (but quite complex query)

1) A, B, C, D, E, F, G, H, I.

Now, customer is asking for every row returning above pattern, the new output should be like below :
1) A, B, C, 'D', D
2) A, B, C, 'E', E
3) A, B, C, 'F', F
4) A, B, C, 'G', G
5) A, B, C, 'H', H
6) A, B, C, 'I', I

Basically, the 1st 3 columns will be repeated in all the 6 NEW ROWS.
The procedure repeats for every row in the original query.

Is this possible ? If yes, how ?

Answer 1

You just need unpivot clause to show the data vertically :

with t(a,b,c,d,e,f,g,h,i) as
(
 select 1,2,3,'D','E',2,3,'X','Y' from dual 
)
select a,b,c,val from
(
select a,b,c,to_char(d) as d, to_char(e) as e, to_char(f) as f, to_char(g) as g, 
             to_char(h) as h, to_char(i) as i
  from t
)  
unpivot 
( val for col in (d,e,f,g,h,i) )
order by col

Demo

to_char() conversions are implemented against getting ORA-01790: expression must have same datatype as corresponding expression error.