postgresql pivot with multiple columns having data

user_11286815 0
Viewed 1030

i want to pivot/crosstab below table

source data

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

enter image description here

pivot group-by postgresql sql
2 Answers

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;
user_142366220

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
user_106767160
Related