CODEWITHSUNDEEP
sqlpostgresqlselect
octobot
octobot

Reputation: 89

Complex Select in PostgreSQL

How to combine multiple rows in a table into a single row with some custom characters ?

for describe my problem I will show a sample table and data,

create table bar ( 
        foo text
      );

insert into bar values ('ABC'),
                       ('DEF'),
                       ('UVW'),
                       ('XYZ');

and I need to get a result that look like below

1_ABC, 2_DEF, 3_UVW, 4_XYZ

Upvotes: 0

Views: 54

Answers (1)

Vivek S.
Vivek S.

Reputation: 21915

You can achieve your desired result with the help of row_number() and string_agg functions in PostgreSQL

As per Document,

row_number() : number of the current row within its partition, counting from 1

string_agg(expression, delimiter): input values concatenated into a string, separated by delimiter

select string_agg(col,', ') result 
from (
       select row_number() over() ||'_'|| foo  col 
       from bar
     ) t

Upvotes: 1

Related Questions