Search code examples
sqlpostgresqlpivotcrosstab

Scatter multiple rows having duplicate columns to single unique row in postgresql

how to scatter multiple duplicate rows into one row in sql/postgresql.

For example --->

lets i am getting 3 rows of

col1  col2   col3
-------------------
11    test   rat
11    test   cat
11    test   test

I want something like this

col1   col2   col3  col4
------------------------
11     test   rat   cat

Its the same thing like groupby in lodash. But how do I achieve the same in postgresql query?

Solution

  • You're looking for crosstab

    postgres=# create table ab (col1 text, col2 text, col3 text);
CREATE TABLE
postgres=# insert into ab values ('t1','test','cat'),('t1','test','rat'),('t1','test','test');
INSERT 0 3
postgres=# select * from crosstab('select col1,col2,col3 from ab') as (col1 text, col2 text, col3 text, col4 text);
 col1 | col2 | col3 | col4 
------+------+------+------
 t1   | cat  | rat  | test
(1 row)

    Disclosure: I work for EnterpriseDB (EDB)