0

I have select statement like this.

select 'A','B','C' from dual;

It display

'A' 'B' 'C'
 A   B   C

I want it show like this

'A'

 A

 B

 C

Please help me.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Khmer Music 4U
  • 9
  • 2
  • have you looked at this http://www.dba-oracle.com/t_converting_rows_columns.htm ? – Alexandru Cimpanu Jun 17 '15 at 06:50
  • 1
    possible duplicate of [How to convert Columns into Rows in Oracle?](http://stackoverflow.com/questions/19858079/how-to-convert-columns-into-rows-in-oracle) – Alexandru Cimpanu Jun 17 '15 at 06:51
  • @AlexandruCimpanu 1>That site doesn't have a good reputation. Not everything that shows on top in Google indexing is good. 2>The examples given in that site will confuse amateurs betwen **string aggregation** and standard **pivot/unpivot**. – Lalit Kumar B Jun 17 '15 at 07:10
  • @AlexandruCimpanu I don't see a reason to use **PIVOT** here. Why do you think it is a duplicate question? Just because of the question title? – Lalit Kumar B Jun 17 '15 at 07:13
  • @LalitKumarB the user has not searched for a solution, I don't see why is it so wrong to point him to some examples instead of giving him the solution, like you did. I was just showing him that this topic has been discussed both on stackoverflow as well as on other sites. – Alexandru Cimpanu Jun 17 '15 at 07:16
  • @AlexandruCimpanu You didn't understand what I said. The site you pointed to is not at all good especially for someone who is still learning. Just making you aware of it. As I said, **string aggregation and standard pivot/unpivot are not the same**. And I saw your vote to close as duplciate, however, I don't see how is this a duplicate. My posted solution is completely different from all the answers in your duplicate question. – Lalit Kumar B Jun 17 '15 at 07:19
  • @LalitKumarB let's end this discussion here. We all have the right to an opinion. I don't think the user red anything, good or bad. And giving him the answer just to get the rep instead of helping him learn it's your choice. I don't think your answer is the only possible one nor that the question is that hard that an experienced user like you had to give an answer right away. So keep calm and have a nice day. – Alexandru Cimpanu Jun 17 '15 at 07:26
  • @AlexandruCimpanu *We all have the right to an opinion* I have nothing against your opinion. I was talking about the site you pointed to. Anyway, no problem. It's up to you whether or not to take the advice. Nice day to you too! – Lalit Kumar B Jun 17 '15 at 07:29

1 Answers1

0

Simply concatenate it with CHR(10) i.e. a line feed.

For example,

SQL> WITH DATA(a, b, c) AS(
  2  SELECT 'A','B','C' FROM dual
  3  )
  4  SELECT a||chr(10)||b||chr(10)||c text FROM DATA;

TEXT
-----
A
B
C


SQL>

Or, you could use UNION ALL.

SQL> column text format a4
SQL> WITH DATA(a, b, c) AS(
  2  SELECT 'A','B','C' FROM dual
  3  )
  4  SELECT a text FROM DATA UNION ALL
  5  SELECT b text FROM DATA UNION ALL
  6  SELECT c text FROM DATA;

TEXT
----
A
B
C

SQL>
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • @KhmerMusic4U Please mark it answered, would help others. Have a look at Stack Overflow tour to understand how this site works. Welcome to SO! – Lalit Kumar B Jun 19 '15 at 03:16