skip to Main Content

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   fk_id  city
1    1      ny
2    2      ny
3    2      ca
4    1      ca

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 fk_id  Q_1 Q_2 Q_3  Q_4 Q_5  Q_6 Q_7 Q_8    Q_9    Q_10  
1   1     n   n   n    n   n     n  n   0.08    n     0.001
2   1     n  0.6  n    n   n     n  n     n     n      n
3   1     0.9  n   n    n   n    n  n     n     n      n
4   1     n   n   n    n    n     n  n     n     0.1     n
5   2     n   n   n    n    n     n  n    0.08    n     0.001
6   2     n   0.6  n    n   n    n  n     n      n      n
7   2     0.9  n   n    n   n    n  n     n     n       n
8   2     n   n   n    n   n     n  n     n     0.1     n


with data as (
  select me.id,me.fk_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, fk_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, fk_id;
I tried the above query but i get the results as


id fk_id  Q_1 Q_2 Q_3  Q_4 Q_5  Q_6 Q_7 Q_8    Q_9    Q_10  
1   1     n   n   n    n   n     n  n   0.08    n     0.001
2   1     n  0.6  n    n   n     n  n     n     n      n
3   1     0.9  n   n    n   n    n  n     n     n      n
4   1     n   n   n    n    n     n  n     n     0.1     n
5   1     n   n   n    n    n     n  n    0.08    n     0.001
6   1     n   0.6  n    n   n    n  n     n      n      n
7   1     0.9  n   n    n   n    n  n     n     n       n
8   1     n   n   n    n   n     n  n     n     0.1     n



I HAVE EDITED THE ABOVE QUESTION The second column has the same values INSTEAD OF UNIQUE fk_id

2

Answers


  1. 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).

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search