I want to create pivot table or cross tab for POSTGRESQL. Any help would be appreciated-CodePudding

I need to join all the three tables as mentioned below

datatype
id     code
1      Q_1
2      Q_2
3      Q_3
4      Q_4
5      Q_5
6      Q_6
7      Q_7
8      Q_8
9      Q_9
10     Q_10

model   
id        datatype_id     values   model_ex_id
1          10             0.001      1
2          8             0.008       1
3          9              0.1        4
4          1              0.9        3
5          2              0.6        2

model_ex  
id   year
1    1995
2    1996
3    1995
4    2008

This is the final table should like after doing cross tab or pivot. I tried in many ways using cross tab but nothing working. Thanks for your help

Final_table
id year   Q_1 Q_2 Q_3  Q_4 Q_5 Q_6 Q_7 Q_8    Q_9    Q_10  
1  1995   n   n   n    n   n    n  n   0.08    n     0.001
2  1996   n  0.6  n    n   n    n  n     n     n      n
3  1995  0.9  n   n    n   n    n  n     n     n      n
4  2008   n   n   n    n   n    n  n     n     0.1     n

CodePudding user response：

This can be done using filtered aggregation:

with data as (
  select me.id, d.code, m."values"
  from model_ex me
    join model m on me.id = m.model_ex_id
    join datatype d on d.id = m.datatype_id
)
select id, 
       max("values") filter (where code = 'Q_1') as q_1,
       max("values") filter (where code = 'Q_2') as q_2,
       max("values") filter (where code = 'Q_3') as q_3,
       max("values") filter (where code = 'Q_4') as q_4,
       max("values") filter (where code = 'Q_5') as q_5,
       max("values") filter (where code = 'Q_6') as q_6,
       max("values") filter (where code = 'Q_7') as q_7,
       max("values") filter (where code = 'Q_8') as q_8,
       max("values") filter (where code = 'Q_9') as q_9,
       max("values") filter (where code = 'Q_10') as q_10
from data       
group by id;

Online example

CodePudding user response：

Doing so is ridiculously hard.

CREATE TABLE datatype(id serial , code text);
INSERT INTO datatype
SELECT  r,format('Q_%s' ,r)
FROM generate_series(1, 10) r;

CREATE TABLE model (id serial , dtid serial , value numeric , exid serial);
INSERT INTO model
SELECT  unnest(ARRAY[1,2 ,3,4 ,5])
       ,unnest(ARRAY[10,8 ,9,1 ,2])
       ,unnest(ARRAY[0.001,0.008,0.1,0.9,0.6])
       ,unnest(ARRAY[1,1 ,4,3 ,2]);

CREATE EXTENSION crosstab;

SELECT  *
FROM crosstab
( $x$
    WITH a AS
    (
        SELECT  exid, dt.id, dt.code ,SUM(value)
        FROM datatype dt
        JOIN model m
        ON m.dtid = dt.id
        GROUP BY 1,2,3
        ORDER BY 1,2
    ) , b AS
    (
        SELECT  exid
        FROM a
        GROUP BY  1
    )
    SELECT  b.exid, dt.code, coalesce(a.sum, 0)
    FROM b
    JOIN datatype dt
    ON true
    LEFT JOIN a
    ON a.exid = b.exid AND a.id = dt.id
    ORDER BY 1, 2$x$
) AS (exid serial , q1 numeric , q2 numeric , q3 numeric , q4 numeric , q5 numeric , q6 numeric , q7 numeric , q8 numeric , q9 numeric , q10 numeric );

Be warned, you have to put together the end of the query (result type) in program. If it would be psql, then it wolud look like this:

SELECT format(
        $omfg$
        SELECT * FROM crosstab(
                $x$WITH a as (
                    select exid,
                        dt.id,
                        dt.code,
                        sum(value)
                    FROM datatype dt
                        JOIN model m ON m.dtid = dt.id
                    group by 1,2,3
                    order by 1,2
                ),
                b as (
                    SELECT exid
                    FROM a
                    GROUP BY 1
                )
                SELECT b.exid,
                    dt.code,
                    coalesce(a.sum, 0)
                FROM b
                    JOIN datatype dt ON true
                    LEFT JOIN a ON a.exid = b.exid
                    AND a.id = dt.id
                order by 1,2 $x$
            ) as (exid serial, %s) $omfg$,
            (
                SELECT array_to_string(array_agg(code || ' numeric'), ', ')
                FROM (
                        SELECT code
                        FROM datatype
                        order by id
                    ) c
            )
    ) \ gexec

Before you try to understand the code above, you should read the docs about the crosstab function here: https://www.postgresql.org/docs/11/tablefunc.html (or from the docs of the appropriate PG version)

You may notice the coalesce(a.sum,0) instead of a.sum in first version. In most cases nicer to use coalesce, but in the question was some n-s in the table, they probably means NULL, without the coalesce you can have them (obviously).