SELECT result in a single row

Question

I am trying to show all the names of the columns in a table, separated by a comma in a single row and then save them in a variable.

Which is the best way?

I have somethig like this:

SELECT tab.column_name || decode(lead(tab.column_name,1) over (order by tab.column_id),null,'',',')
FROM
    all_tab_columns tab,
    all_constraints pk,
    all_cons_columns pk_det
WHERE
    tab.table_name = pk.table_name (+)
    and pk.table_name = pk_det.table_name (+)
    and tab.owner = pk.owner (+)
    and pk.owner = pk_det.owner (+)
    and pk.constraint_name = pk_det.constraint_name (+)
    and tab.column_name = pk_det.column_name (+)
    and tab.data_type not like '%LOB%'
    and tab.owner = :OWNER_SOURCE
    and tab.table_name = :TABLE_NAME_SOURCE

And the result is this:

ID,
_LOCALE,
LABEL,
OBSERVATIONS

But... I need this way:

ID,_LOCALE,LABEL,OBSERVATIONS

Thanks in advance.

See LISTAGG https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030 — Serg, Jun 24 '19 at 09:14
Get rid of the outdated `(+)` operator for left joins. Use the standard `LEFT JOIN` syntax — Kaushik Nayak, Jun 24 '19 at 09:33
Thanks @Serg for the suggestion. I used the function LISTAGG and it has solved the doubt. Thanks! — mozas, Jun 24 '19 at 12:11

score -1 · Answer 1 · answered Jun 24 '19 at 08:54

You can use the substring function. see below the sample code on how to concatenate the rows into one row

select SUBSTRING
 (( 
    SELECT ', ' + s.Column_name
    FROM INFORMATION_SCHEMA.COLUMNS AS s
    WHERE TABLE_NAME = N'TBLACCESS'
    FOR XML PATH('')
 ), 2, 1000)

if you want to save in variable. just add the the variable name in the substring.

select @myvariable=SUBSTRING
 (( 
    SELECT ', ' + s.Column_name
    FROM INFORMATION_SCHEMA.COLUMNS AS s
    WHERE TABLE_NAME = N'TBLACCESS'
    FOR XML PATH('')
 ), 2, 1000)

Thanks for the answer @Vic but I used the function LISTAGG and it works. — mozas, Jun 24 '19 at 12:12

SELECT result in a single row

1 Answers1