Get each row of the resultset as one comma separated string-CodePudding

Lets say i have the following table:

id    col1  col2  col3
--    ----  ---- -----
1      A      A     A
2      B      B     B
3      C      C     C

I want an sql query to create a resultset concatenating all the columns into one comma separated string but without specify the columns (col1, col2, col3) in the selection:

'A', 'A', 'A'
'B', 'B', 'B'
'C', 'C', 'C'

So far i have tried the following but this brings me just one row with all the rows in one string:

select concat('''', string_agg(value, ''','''), '''') as res
from (
    select (json_each_text(row_to_json(test_table))).value
    from test_table
) x

Result: 'A', 'A', 'A','B', 'B', 'B','C', 'C', 'C'

CodePudding user response：

Assuming the id is unique, you should group the rows by it, e.g.

select id, string_agg(quote_literal(value), ',')
from test_table t
cross join jsonb_each_text(to_jsonb(t)- 'id')
group by id
order by id

Test it in Db<>Fiddle.

CodePudding user response：

You need to group by a unique column in order to get one row per original row:

select id, string_agg(val, ',')
from (
  select id, r.val
  from test_table t 
    cross join jsonb_each_text(to_jsonb(t) - 'id') as r(key, val)
) x
group by id
order by id;

If a JSONB array of all column values is an alternative you can live with, this is a bit simpler:

select id, 
       jsonb_path_query_array(to_jsonb(t) - 'id', '$.keyvalue().value')
from test_table
order by id;