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  
Post a Comment