I have the following tables:
kid
id
name
kid_workshop
id
kid_id
workshop_name
workshop_name could be only: arts, martial_arts, chess, soccer
in order to increase performance, I want to create a materialized view that will look like this:
kid_id name arts martial_arts chess soccer
1 Dann True True False True
How can I do it? I am using postgres
Try the following query:
create materialized view your_view as
select
k.id,
k.name,
max(case when kw.workshop_name = 'arts'
then 'true' else 'false' end) arts,
max(case when kw.workshop_name = 'martial_arts'
then 'true' else 'false' end) martial_arts,
max(case when kw.workshop_name = 'chess'
then 'true' else 'false' end) chess,
max(case when kw.workshop_name = 'soccer'
then 'true' else 'false' end) soccer
from kid k
inner join kid_workshop kw
on k.id = kw.kid_id
group by k.id,
k.name
This query employs a trick while doing the pivot. Specifically, it assigns the string 'true'
for a positive case and 'false'
to every negative case. Since 'true'
is lexicographically greater than 'false'
, it should be retained using the MAX()
function if present, otherwise false would be reported. This is precisely the logic we want.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments