Geek Answers Handbook

Concat sql query result using field delimiter

I have a request:

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

(rownum will change every time you specify only one record at a time)

column1      column2       column3
alpha         beta          gamma

I need a result like:

alpha,beta,gamma

All I need to do is field separators between the pins.

This does not apply to the proposed link below and is not duplicated!

+4
source share
3 answers

There are two ways:

Function concat

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 is that you do not need to use single quotes around the ROWNUM value , it unnecessarily converts it to a stringjust leave it as NUMBER .

+3
source

If LISTAGGnot what you are looking for DUMMYCOLmay help .--

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

: -

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

More articles:


All Articles