Reformatting table in SQL-CodePudding

I have a table in sql

is there a way to reformat this table with contract_kind as rows and percentile values as columns without me having to create 4 tables with where clauses and joining them. Using Postgres.

CodePudding user response：

TLDR: just use the crosstab function as per documentation

Long reply: I recreated a similar case with

create table test (id int, contract_kind text, percentile int, cut_off_time float);

insert into test values
    (1,'TEMPLATE',25,1.91),
    (2,'TEMPLATE',50,51.93),
    (3,'TEMPLATE',75,158.41),
    (4,'TEMPLATE',90,343.01),
    (5,'TEMPLATE_EDITABLE',25,26),
    (6,'TEMPLATE_EDITABLE',50,27),
    (7,'TEMPLATE_EDITABLE',75,28),
    (8,'TEMPLATE_EDITABLE',90,29),
    (9,'UPLOAD_EDITABLE',25,10),
    (10,'UPLOAD_EDITABLE',50,20),
    (11,'UPLOAD_EDITABLE',75,30),
    (12,'UPLOAD_EDITABLE',90,40),
    (13,'UPLOAD_SIGN',25,40),
    (14,'UPLOAD_SIGN',50,77),
    (15,'UPLOAD_SIGN',75,99),
    (16,'UPLOAD_SIGN',90,133);

result:

 id |   contract_kind   | percentile | cut_off_time
---- ------------------- ------------ --------------
  1 | TEMPLATE          |         25 |         1.91
  2 | TEMPLATE          |         50 |        51.93
  3 | TEMPLATE          |         75 |       158.41
  4 | TEMPLATE          |         90 |       343.01
  5 | TEMPLATE_EDITABLE |         25 |           26
  6 | TEMPLATE_EDITABLE |         50 |           27
  7 | TEMPLATE_EDITABLE |         75 |           28
  8 | TEMPLATE_EDITABLE |         90 |           29
  9 | UPLOAD_EDITABLE   |         25 |           10
 10 | UPLOAD_EDITABLE   |         50 |           20
 11 | UPLOAD_EDITABLE   |         75 |           30
 12 | UPLOAD_EDITABLE   |         90 |           40
 13 | UPLOAD_SIGN       |         25 |           40
 14 | UPLOAD_SIGN       |         50 |           77
 15 | UPLOAD_SIGN       |         75 |           99
 16 | UPLOAD_SIGN       |         90 |          133
(16 rows)

Now to use the crosstab you need to create the tablefunc extension.

create extension tablefunc;

and then you can use it to pivot the data

select * from 
crosstab('select percentile, contract_kind, cut_off_time from test order by 1,2') 
as ct(percentile int, template float, template_editable float, upload_editable float, upload_sing float);

result

 percentile | template | template_editable | upload_editable | upload_sing
------------ ---------- ------------------- ----------------- -------------
         25 |     1.91 |                26 |              10 |          40
         50 |    51.93 |                27 |              20 |          77
         75 |   158.41 |                28 |              30 |          99
         90 |   343.01 |                29 |              40 |         133
(4 rows)