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
Doing so is ridiculously hard.
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:
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 ofa.sum
in first version. In most cases nicer to use coalesce, but in the question was somen
-s in the table, they probably meansNULL
, without thecoalesce
you can have them (obviously).This can be done using filtered aggregation:
Online example