Friday, November 2, 2012

Convert Multiple Row output from SELECT query into Single Row in PostgreSQL


Here is the simple query to convert the result of select query from multiple rows in a table to display as single row,


Simple SELECT query

SELECT str_empno FROM employee;

will return the output like,


Output:
str_empno
-------------
4400100476
4400100563
4400101130
4400101432
4400101501
4400101527
4400101724

by using array_agg and array_to_string function in PostgreSQL,  we can get aggregates/combine/concatenate multiple rows into one row

SELECT array_to_string( array_agg(str_empno),',' ) as  empno FROM employee;

this will return 
Output:

str_empno
-------------

4400100476,4400100563,4400101130,4400101432,4400101501,4400101527,4400101724  

4 comments:

Drew Rash said...

Hey thanks, needed this.

solaimurugan.v said...

@Drew Rash, welcome

Teja Ram Seervi said...

Is there any limit that how many rows can be concatenated?


I mean I have around 10 lacs of rows.I can concatenate all of them as suggested?

solaimurugan v said...

yes, you can do. but it would cast your time.