postgresql pivot with multiple columns having data

Question

i want to pivot/crosstab below table

I tried below query in postgresql but it gives error ERROR: invalid return type DETAIL: Query-specified return tuple has 6 columns but crosstab returns 5

 SELECT * FROM crosstab(
'select Key,status,v_text,v_number,v_dob from table 
    where type = ''First_Name'' or type = ''Last_Name''  or type = ''DOB'' or type = ''Contact'' order by 1',
'select distinct type  from table where type = ''First_Name'' or type = ''Last_Name'' or type = ''DOB'' or type = ''Contact'' order by 1')
 AS ct( Key int, First_Name text,   Last_Name  text, DOB date, Contact int);

required output is like below which has all types with values. Is it possible in pgsql

GMB · Accepted Answer · 2020-09-08 20:24:39Z

4

You can do it like below -

select  key,
  status,
  max(case when type = 'First name' then v_text end) As first_name,        
  max(case when type = 'Last name' then v_text end) As last_name, 
  max(case when type = 'DOB' then v_date end) As DOB,
  max(case when type = 'Contact' then v_number end) As Contact 
from mytable 
group by key, status;

edited Sep 8, 2020 at 20:24

GMB

224k25 gold badges103 silver badges151 bronze badges

answered Sep 8, 2020 at 17:10

Somy

1,6441 gold badge7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nick · Accepted Answer · 2020-09-09 01:07:07Z

2

I would recommend conditional aggregation. This is standard SQL syntax, and it is also much more flexible than vendor-specific implementations such as crosstab:

select
    key,
    status,
    max(v_text)   filter(where type = 'FistName') first_name,
    max(v_text)   filter(where type = 'LastName') last_name,
    max(v_date)   filter(where type = 'DOB')      dob,
    max(v_number) filter(where type = 'Contact') contact
from mytable
group by key, status

edited Sep 9, 2020 at 1:07

Nick

147k23 gold badges67 silver badges106 bronze badges

answered Sep 8, 2020 at 16:34

GMB

224k25 gold badges103 silver badges151 bronze badges

1 Comment

Nick Over a year ago

Although filter is part of the standard, it is an optional feature and is not widely supported (I think only PostgreSQL and SQLite support it).

Collectives™ on Stack Overflow

postgresql pivot with multiple columns having data

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related