Oracle SQL convert a single row with multiple columns into multiple rows

Question

I would like to take the following query

select '2009042 Restraint 151214.pdf', 
       '2009042 Restraint 170215.pdf',
       '2009042 Restraint 240215.pdf',
       '2009856 Restraint 190215.pdf',
       '208272 Notice 120215.pdf',
       '208272 Restraint 120215.pdf',
       '212598 Restraint 160215.pdf',
       '213195 Notice 130215.pdf'
from   dual

and convert it into a query that returns the columns from the above query as rows. The pivot statement doesn't seem to be able to do this.

So I would like to return the rows:

COL1
2009042 Restraint 151214.pdf
2009042 Restraint 170215.pdf
2009042 Restraint 240215.pdf
2009856 Restraint 190215.pdf
208272 Notice 120215.pdf
208272 Restraint 120215.pdf
212598 Restraint 160215.pdf
213195 Notice 130215.pdf

Note that there is a slight difference from inverse row to column

possible duplicate of inverse row to column

Politank-Z
– Politank-Z

2015-04-20 12:05:38 +00:00
Commented Apr 20, 2015 at 12:05 — Politank-Z
– Politank-Z, Commented Apr 20, 2015 at 12:05

Community · Accepted Answer · 2017-05-23 12:26:39Z

The pivot statement doesn't seem to be able to do this.

Yes, because you're not trying to pivot your rows into columns, you're trying to unpivot them from columns into rows.

If your columns had a predefined, enumerated set of names, you could use UNPIVOT like this:

select *
from (
  select '2009042 Restraint 151214.pdf' v1, 
         '2009042 Restraint 170215.pdf' v2,
         '2009042 Restraint 240215.pdf' v3,
         '2009856 Restraint 190215.pdf' v4,
         '208272 Notice 120215.pdf'     v5,
         '208272 Restraint 120215.pdf'  v6,
         '212598 Restraint 160215.pdf'  v7,
         '213195 Notice 130215.pdf'     v8
  from   dual
) t
unpivot (
  col1 for v in (v1, v2, v3, v4, v5, v6, v7, v8)
)

Yielding:

V   COL1
--------------------------------
V1  2009042 Restraint 151214.pdf
V2  2009042 Restraint 170215.pdf
V3  2009042 Restraint 240215.pdf
V4  2009856 Restraint 190215.pdf
V5  208272 Notice 120215.pdf
V6  208272 Restraint 120215.pdf
V7  212598 Restraint 160215.pdf
V8  213195 Notice 130215.pdf

If no such names are available, things get a bit more hairy, but still doable (probably depending on not well-defined column naming conventions in Oracle):

select *
from (
  select '2009042 Restraint 151214.pdf', 
         '2009042 Restraint 170215.pdf',
         '2009042 Restraint 240215.pdf',
         '2009856 Restraint 190215.pdf',
         '208272 Notice 120215.pdf',
         '208272 Restraint 120215.pdf',
         '212598 Restraint 160215.pdf',
         '213195 Notice 130215.pdf'
  from   dual
) t
unpivot (
  col1 for v in (
    "'2009042RESTRAINT151214.PDF'", 
    "'2009042RESTRAINT170215.PDF'", 
    "'2009042RESTRAINT240215.PDF'", 
    "'2009856RESTRAINT190215.PDF'", 
    "'208272RESTRAINT120215.PDF'", 
    "'208272NOTICE120215.PDF'", 
    "'212598RESTRAINT160215.PDF'", 
    "'213195NOTICE130215.PDF'"
  )
)

Yielding:

V                               COL1
------------------------------------------------------------
'2009042RESTRAINT151214.PDF'    2009042 Restraint 151214.pdf
'2009042RESTRAINT170215.PDF'    2009042 Restraint 170215.pdf
'2009042RESTRAINT240215.PDF'    2009042 Restraint 240215.pdf
'2009856RESTRAINT190215.PDF'    2009856 Restraint 190215.pdf
'208272RESTRAINT120215.PDF'     208272 Restraint 120215.pdf
'208272NOTICE120215.PDF'        208272 Notice 120215.pdf
'212598RESTRAINT160215.PDF'     212598 Restraint 160215.pdf
'213195NOTICE130215.PDF'        213195 Notice 130215.pdf

Of course, if you're ready to write that much SQL anyway, why not just use UNION ALL, as Gordon suggested

Gordon Linoff · Accepted Answer · 2015-04-20 12:05:18Z

1

Just phrase the query as a union all in the first place:

select '2009042 Restraint 151214.pdf' from dual union all
select '2009042 Restraint 170215.pdf' from dual union all
select '2009042 Restraint 240215.pdf' from dual union all
select '2009856 Restraint 190215.pdf' from dual union all
select '208272 Notice 120215.pdf' from dual union all
select '208272 Restraint 120215.pdf' from dual union all
select '212598 Restraint 160215.pdf' from dual union all
select '213195 Notice 130215.pdf' from dual

answered Apr 20, 2015 at 12:05

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Ooberdan · Accepted Answer · 2017-02-02 10:41:50Z

A quick way of doing this is using the following of Oracle's system defined type, sys.odcivarchar2list:

select column_value col1 
from table(sys.odcivarchar2list(
       '2009042 Restraint 151214.pdf', 
       '2009042 Restraint 170215.pdf',
       '2009042 Restraint 240215.pdf',
       '2009856 Restraint 190215.pdf',
       '208272 Notice 120215.pdf',
       '208272 Restraint 120215.pdf',
       '212598 Restraint 160215.pdf',
       '213195 Notice 130215.pdf'));

This returns:

COL1                                                                           
--------------------------------------------------------------------------------
2009042 Restraint 151214.pdf                                                    
2009042 Restraint 170215.pdf                                                    
2009042 Restraint 240215.pdf                                                    
2009856 Restraint 190215.pdf                                                    
208272 Notice 120215.pdf                                                        
208272 Restraint 120215.pdf                                                     
212598 Restraint 160215.pdf                                                     
213195 Notice 130215.pdf

There's sys.odcinumberlist() if you want to use numerical values, and others documented in the Oracle doc linked above.

Collectives™ on Stack Overflow

Oracle SQL convert a single row with multiple columns into multiple rows

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related