Write a dynamic query of nested subqueries [duplicate]

Question

Let's take an everyday query with a list of nested queries so I can show you the problem, see how we'd have to add a subquery when a new Certification comes out:

select DISTINCT e.display_name as "Name",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 1 limit 1),0) "Practitioner",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 2 limit 1),0) "AlexaSkillBuilder",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 3 limit 1),0) "SolutionArchitectAssociate",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 4 limit 1),0) "DeveloperAssociate",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 5 limit 1),0) "SysopsAssociate",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 6 limit 1),0) "SolutionArchitectPro",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 7 limit 1),0) "DevopsPro",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 8 limit 1),0) "Security",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 9 limit 1),0) "Networking",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 10 limit 1),0) "BigData",
coalesce((select 1 from qualification q where q.employeeid = e.id and certificationid = 11 limit 1),0) "MachineLearning"
from employee e
inner join qualification q on q.employeeid = e.id
group by e.id, e.display_name;

The output of this data is a report that looks like this, eg:

+------+--------------+-------+-----+-----+-----+--------+---------+-----+------------+---------+----+
| Name | Practitioner | Alexa | SAA | DEV | SOA | DevOps | ArchPro | Sec | Networking | BigData | ML |
+------+--------------+-------+-----+-----+-----+--------+---------+-----+------------+---------+----+
| Joe  | 1            | 0     | 0   | 0   | 0   | 1      | 0       | 0   | 0          | 0       | 0  |
+------+--------------+-------+-----+-----+-----+--------+---------+-----+------------+---------+----+
| Jane | 1            | 1     | 0   | 0   | 0   | 0      | 0       | 0   | 0          | 0       | 0  |
+------+--------------+-------+-----+-----+-----+--------+---------+-----+------------+---------+----+

How can I write this query so I don't need to edit it each time a new certification comes out?

---

Schema and sample data below in Postgres:

CREATE TABLE certification (
    id serial NOT NULL,
    officialcertname text NOT NULL,
    "name" text NOT NULL,
    vendorid int4 NOT NULL DEFAULT 1,
    isdeleted bool NOT NULL DEFAULT false,
    CONSTRAINT certification_pkey PRIMARY KEY (id)
);

CREATE TABLE qualification (
    id serial NOT NULL,
    employeeid int4 NOT NULL,
    certificationid int4 NOT NULL,
    date_attained timestamptz NULL,
    date_expiry timestamptz NULL,
    certurl text NULL,
    verified bool NOT NULL DEFAULT false,
    created_by text NOT NULL,
    created_date timestamptz NOT NULL,
    modified_by text NULL,
    modified_date timestamptz NULL,
    CONSTRAINT qualification_pkey PRIMARY KEY (id)
);


INSERT INTO certification (officialcertname,"name",vendorid,isdeleted) VALUES 
('AWS Certified Cloud Practitioner (CLF)','AWS Cloud Practitioner',1,false)
,('AWS Certified Alexa Skill Builder','AWS Alexa Skill Builder',1,false)
,('AWS Certified Solutions Architect - Associate (SAA)','AWS Solution Architect Associate',1,false)
,('AWS Certified Developer - Associate (DVA)','AWS Developer Associate',1,false)
,('AWS Certified SysOps Administrator - Associate (SOA)','AWS SysOps Associate',1,false)
,('AWS Certified Solutions Architect - Professional (SAP)','AWS Solution Architect Professional',1,false)
,('AWS Certified DevOps Engineer - Professional (DOP)','AWS DevOps Professional',1,false)
,('AWS Certified Security - Specialty (SCS)','AWS Security',1,false)
,('AWS Certified Networking - Specialty (SNS)','AWS Networking',1,false)
,('AWS Certified Big Data - Specialty','AWS Big Data',1,false)

---

This is not a duplicate of the question I asked yesterday: A Query with a Dynamic Case clause or how to avoid Case When, because that's to do with CASE WHEN, this is about subqueries.

Answer 1

This article was super helpful: https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html and the demo of particular use: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=407a37686238bb3fbcbc4285d1705871

Unfortunately it turns out using a Pivot/CrossTab is much more complex than dynamically generating the original query with code:

SELECT employeeid, coalesce(GCPAssociateCloudEngineer ,0) as GCPAssociateCloudEngineer,coalesce(GCPProfessionalCloudArchitect ,0) as GCPProfessionalCloudArchitect,coalesce(GCPProfessionalCloudSecurityEngineer,0) as GCPProfessionalCloudSecurityEngineer,coalesce(GCPProfessionalDataEngineer ,0) as GCPProfessionalDataEngineer,coalesce(GCPProfessionalCloudDeveloper ,0) as GCPProfessionalCloudDeveloper,coalesce(GCPProfessionalNetworkEngineer,0) as GCPProfessionalNetworkEngineer
FROM crosstab('
SELECT employeeid, c.name as "name",1 as complete FROM qualification q
inner JOIN certification AS c ON c.id = q.certificationid where c.vendorid = 3 order by 2
',  $$VALUES ('GCPAssociateCloudEngineer'), ('GCPProfessionalCloudArchitect'), ('GCPProfessionalCloudSecurityEngineer'), ('GCPProfessionalDataEngineer'), ('GCPProfessionalCloudDeveloper'), ('GCPProfessionalNetworkEngineer')$$
) 
 AS final_result(employeecertregid int4,GCPAssociateCloudEngineer int4,GCPProfessionalCloudArchitect int4,GCPProfessionalCloudSecurityEngineer int4,GCPProfessionalDataEngineer int4,GCPProfessionalCloudDeveloper int4,GCPProfessionalNetworkEngineer int4);