OraFAQ Forum: SQL & PL/SQL » want data of all columns in a single column (no concatenate)

Home » SQL & PL/SQL » SQL & PL/SQL » want data of all columns in a single column (no concatenate) (oracle 10g/11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

want data of all columns in a single column (no concatenate) [message #637277]

Thu, 14 May 2015 06:52

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

>create table test(id number,val varchar2(10))

>insert into test values(1,'A')
>insert into test values(2,'B')
>insert into test values(3,'C')
>insert into test values(4,'D')
>insert into test values(5,'E')

output required:-

1
2
3
4
5
A
B
C
D
E

Plese help thanks.

Report message to a moderator

Re: want data of all columns in a single column (no concatenate) [message #637279 is a reply to message #637277]

Thu, 14 May 2015 07:12

Bill B
Messages: 1971
Registered: December 2004

Senior Member

select id
from test
order by id
union all
select val
from test
order by val;

Report message to a moderator

Re: want data of all columns in a single column (no concatenate) [message #637281 is a reply to message #637279]

Thu, 14 May 2015 07:23

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

No Bill, that is incorrect syntax.

1. Data types are different.
2. ORDER BY is tricky with UNION ALL

[Updated on: Thu, 14 May 2015 07:25]

Report message to a moderator

Re: want data of all columns in a single column (no concatenate) [message #637282 is a reply to message #637281]

Thu, 14 May 2015 07:27

Bill B
Messages: 1971
Registered: December 2004

Senior Member

oops your right. Only one order by. I will correct.

select id
from
(select id
from test
order by id)
union all
select val
from
(
select val
from test
order by val);

Report message to a moderator

Re: want data of all columns in a single column (no concatenate) [message #637283 is a reply to message #637279]

Thu, 14 May 2015 07:28

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

@Bill b , are you sure this query is gonna work , when id column is of numeric data type and val is character type. after converting numeric column to char the query is working fine .
thanks for the help.

[Updated on: Thu, 14 May 2015 07:34]

Report message to a moderator

Re: want data of all columns in a single column (no concatenate) [message #637284 is a reply to message #637277]

Thu, 14 May 2015 07:28

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

@jgjeetu,

This will work only with your sample data, as ORDER BY on string will sort it based on the ASCII values. You need to take care of the order depending on the data. So, if you have alphanumeric data, you will need to sort each output in the UNION ALL and not on the entire result set. Using ORDER BY with UNION ALL applies it on the result set.

SQL> SELECT TO_CHAR(id) id FROM TEST
  2  UNION ALL
  3  SELECT val id FROM test ORDER BY id;

ID
----------------------------------------
1
2
3
4
5
A
B
C
D
E

10 rows selected.

SQL>

Report message to a moderator