CODEWITHSUNDEEP
sqloracle-databaseunpivot
anjanb
anjanb

Reputation: 13867

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

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 ?

Upvotes: 2

Views: 107

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65313

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Probably simplest using union all:

select a, b, c, 'D' as which, d as val from t union all
select a, b, c, 'E', e from t union all
select a, b, c, 'F', f from t union all
select a, b, c, 'G', g from t union all
select a, b, c, 'H', j from t union all
select a, b, c, 'I', i from t ;

This is the simplest way, but not the most efficient. It will scan the table once for each column. For not-too-large table, that is probably fine (the table is cached in memory) from a performance perspective.

If the "table", though" is really a view, then performance could be a more significant issue.

Upvotes: 1

Related Questions