?
Solved

Convert Oracle SQL query to single column output

Posted on 2009-04-16
5
Medium Priority
?
740 Views
Last Modified: 2013-12-18
I have a simple two column Oracle table named sample:
head        para
a               1
b               2
c               3
d               4

I need to do a select query so that the output is as follows:

a
1
b
2
c
3
d
4

Any suggestions?

Thank you,

Dray
0
Comment
Question by:dray2009
  • 2
  • 2
5 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 24164286
Hi dray2009,

Do you need it in that order?

select head from sample
union all
select para from sample

will return one column ... but the order will not be the same.

lwadwell
0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 400 total points
ID: 24164571
to get the output exactly as you want, try this.
select col2 
  from (select 'col1' as col1,head as col2,ROW_NUMBER() over(order by (select 1)) as col3 from YourTable union all
        select 'col2' as col1,cast(para as varchar2(2)) as col2,ROW_NUMBER() over(order by (select 1)) as col3 from YourTable) t1 
 order by col3,col1

Open in new window

0
 
LVL 25

Accepted Solution

by:
lwadwell earned 1600 total points
ID: 24164674
@Sharath_123, For the SQL to work in Oracle, you would need to "SELECT 1 FROM DUAL".  More simply in Oracle you can use the ROWNUM pseudo column like below.  Interestingly when I tested the ROW_NUMBER() option it re-ordered the output to
a
1
d
4
...etc

When I replaced the (SELECT 1 FROM DUAL) with 'x' ... the order returned back to as in the question.

SELECT one_col
  FROM (SELECT head AS one_col, ROWNUM      AS sort_key FROM SAMPLE
        UNION ALL
        SELECT para AS one_col, ROWNUM + .5 AS sort_key FROM SAMPLE)
 ORDER BY sort_key;
 
-- Alternative
SELECT one_col
  FROM (SELECT head AS one_col, ROW_NUMBER() OVER(ORDER BY 'x') AS colx, '1' AS coly FROM SAMPLE
        UNION ALL
        SELECT para AS one_col, ROW_NUMBER() OVER(ORDER BY 'x') AS colx, '2' AS coly FROM SAMPLE)
 ORDER BY colx, coly;

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 24164746
lwadwell - thanks.  
don't know the strange behaviour with ROW_NUMBER.
0
 

Author Closing Comment

by:dray2009
ID: 31571278
This is exacctly what I needed. Thank you
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question