MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE people (
  id INT PRIMARY KEY,
  name VARCHAR(20),
  dob DATE,
  requires_cert TINYINT
);
INSERT INTO people VALUES
  (1, 'Person 1', '2000-01-01', 1),
  (2, 'Person 2', '2006-06-06', 0),
  (3, 'Person 3', '1980-01-01', 1);

CREATE TABLE cert_types (
  id INT PRIMARY KEY,
  name VARCHAR(20)
);
INSERT INTO cert_types VALUES
  (1, 'Certification 1'),
  (2,  'Certification 2');

CREATE TABLE certs (
  person_id INT NOT NULL,
  certification_id INT NOT NULL,
  expiration DATE,
  PRIMARY KEY (person_id, certification_id)
);
INSERT INTO certs VALUES
  (1, 1, '2025-01-01'),
  (3, 2, '2066-06-06'),
  (1, 2, '2024-03-30');

Records: 3  Duplicates: 0  Warnings: 0



Records: 2  Duplicates: 0  Warnings: 0



Records: 3  Duplicates: 0  Warnings: 0


SELECT p.*,
  MAX(IF(c.certification_id = 1, c.expiration, NULL)) AS 'Certificate 1',
  MAX(IF(c.certification_id = 2, c.expiration, NULL)) AS 'Certificate 2'  
FROM people p
LEFT JOIN certs c ON p.id = c.person_id
GROUP BY p.id;


id
name
dob
requires_cert
Certificate 1
Certificate 2


1
Person 1
2000-01-01
1
2025-01-01
2024-03-30

2
Person 2
2006-06-06
0
null
null

3
Person 3
1980-01-01
1
null
2066-06-06


SELECT p.*,
  COALESCE(MAX(IF(c.certification_id = 1, c.expiration, NULL)), 'n/a') AS 'Certificate 1',
  COALESCE(MAX(IF(c.certification_id = 2, c.expiration, NULL)), 'n/a') AS 'Certificate 2'  
FROM people p
LEFT JOIN certs c ON p.id = c.person_id
GROUP BY p.id;


id
name
dob
requires_cert
Certificate 1
Certificate 2


1
Person 1
2000-01-01
1
2025-01-01
2024-03-30

2
Person 2
2006-06-06
0
n/a
n/a

3
Person 3
1980-01-01
1
n/a
2066-06-06


SELECT p.*, GROUP_CONCAT(ct.name, ':', c.expiration) AS certificates
FROM people p
LEFT JOIN certs c ON p.id = c.person_id
LEFT JOIN cert_types ct ON c.certification_id = ct.id
GROUP BY p.id;


id
name
dob
requires_cert
certificates


1
Person 1
2000-01-01
1
Certification 1:2025-01-01,Certification 2:2024-03-30

2
Person 2
2006-06-06
0
null

3
Person 3
1980-01-01
1
Certification 2:2066-06-06

id	name	dob	requires_cert	Certificate 1	Certificate 2
1	Person 1	2000-01-01	1	2025-01-01	2024-03-30
2	Person 2	2006-06-06	0	null	null
3	Person 3	1980-01-01	1	null	2066-06-06

id	name	dob	requires_cert	certificates
1	Person 1	2000-01-01	1	Certification 1:2025-01-01,Certification 2:2024-03-30
2	Person 2	2006-06-06	0	null
3	Person 3	1980-01-01	1	Certification 2:2066-06-06