concat result of sql query using field seperator [duplicate]

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!

Answer 1

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.