concat result of sql query using field seperator

Question

I have a query:

SELECT *  FROM (SELECT col1,col2,col3,ROWNUM R 
                FROM TBL WHERE  ROWNUM < 3) 
          WHERE R='2';

(rownum will change each time giving only one record at a time )

column1      column2       column3
alpha         beta          gamma

I need result as:

alpha,beta,gamma

All i need to do is, field separators in between the output.

This is not relevant to the suggested link below and not a duplicate!

take a look [here](http://stackoverflow.com/questions/10954511/oracle-pl-sql-results-into-one-string) — m.cekiera, Jul 09 '15 at 07:39
but I think LISTAGG is returning results of single column and this question has seperate columns — Ashish Patil, Jul 09 '15 at 07:48
No, this is not the duplicate to the LISTAGG topic. OP is asking for **string concatenation** and not **string aggregation**. I am reopening it. — Lalit Kumar B, Jul 09 '15 at 12:36
@LalitKumarB: the OP marked it as duplicate themselves though... — Qantas 94 Heavy, Jul 09 '15 at 13:29
@Qantas94Heavy That's strange, because OP marked my answer as answered, and then accepted the topic to be a duplicate of a completely different topic. I see why robo-user marked it as duplicate. — Lalit Kumar B, Jul 09 '15 at 14:02

score 3 · Answer 1 · answered Jul 09 '15 at 08:08

3

If LISTAGG is not what you are looking for then, DUMMYCOL may help.--

   SELECT *
FROM
  (SELECT col1,
    ','AS DUMMYCOL,
    col2,
    ','AS DUMMYCOL,
    col3,
    ROWNUM R
  FROM TBL
  WHERE ROWNUM < 3
  )
WHERE R='2';

answered Jul 09 '15 at 08:08

Ashish Patil

4,428
1
15
36

score 3 · Answer 2 · answered Jul 09 '15 at 08:12

3

You can use following:-

SELECT *  
FROM (SELECT col1||','||col2||','||col3,ROWNUM R 
      FROM TBL WHERE  ROWNUM < 3) 
WHERE R='2';

answered Jul 09 '15 at 08:12

Ankit Bajpai

13,128
4
25
40

score 3 · Accepted Answer · answered Jul 09 '15 at 08:30

There are two ways:

CONCAT function

SELECT *
FROM
  (SELECT concat(concat(concat(concat('a', ','), col2), ','), col3),
    ROWNUM R
  FROM TBL
  WHERE ROWNUM < 3
  )
WHERE R=2;

|| concatenation operator

SELECT *
FROM
  (SELECT col1||','||col2||','||col3,ROWNUM R FROM TBL WHERE ROWNUM < 3
  )
WHERE R=2;

Another thing, no need to use single quotes around the value for ROWNUM, it unnecessarily converts it into string, just leave it as NUMBER.

concat result of sql query using field seperator

3 Answers3