Superdooperhero Superdooperhero - 4 years ago 95
SQL Question

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

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

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download