Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

concat result of sql query using field seperator [duplicate]

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!

like image 846
VRVigneshwara Avatar asked Jan 09 '23 00:01

VRVigneshwara


1 Answers

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.

like image 107
Lalit Kumar B Avatar answered Jan 10 '23 21:01

Lalit Kumar B