I am attempting to generate a "matrix" (I may be using the term incorrectly here) from 3 tables using a Postgres query.
How can I achieve this using such an SQL query?
Here are the example tables I have at the moment:
Company
+----+-------------+
| id | name |
+----+-------------+
| 1 | 9999999991 |
| 2 | 9999999992 |
| 3 | 9999999993 |
| 4 | 9999999994 |
| 5 | 9999999995 |
| 6 | 9999999996 |
| 7 | 9999999997 |
| 8 | 9999999998 |
+----+-------------+
Services
+----+-------------+
| id | name |
+----+-------------+
| 1 | Service 1 |
| 2 | Service 2 |
| 3 | Service 3 |
| 4 | Service 4 |
+----+-------------+
Service Company Map
+----+----------+---------------+
| id |company | services |
+----+-----------+--------------+
| 1 | 9999999991| 2 |
| 2 | 9999999991| 4 |
| 3 | 9999999992| 1 |
| 4 | 9999999992| 4 |
| 5 | 9999999993| 1 |
| 6 | 9999999993| 3 |
| 7 | 9999999993| 4 |
+----+-----------+--------------+
Here is an example of the matrix I am attempting to generate
+----------+----------+----------+----------+----------+
| | Service 1| Service 2| Service 3| Service 4|
+----------+----------+----------+----------+----------+
| Company 1| - | X | - | X |
| Company 2| X | - | - | X |
| Company 3| X | - | X | X |
| Company 4| - | - | - | - |
+----------+----------+----------+----------+----------+
(Note, I did reference this question, but we seem to be after different things: Postgres query for matrix table)
Update: Basic DDL / Inserts per @SQLpro's request
CREATE TABLE service_client_mappings
( id int NOT NULL,
company_id int NOT NULL,
service_id int NOT NULL,
CONSTRAINT service_client_mappings_pk PRIMARY KEY (id)
);
CREATE TABLE services
( id int NOT NULL,
name char(50) NOT NULL,
CONSTRAINT services_pk PRIMARY KEY (id)
);
CREATE TABLE company
( id int NOT NULL,
name char(50) NOT NULL,
CONSTRAINT company_pk PRIMARY KEY (id)
);
INSERT INTO company
(id, name)
VALUES
(1, 'ACME');
INSERT INTO company
(id, name)
VALUES
(2, 'Target');
INSERT INTO company
(id, name)
VALUES
(3, 'Walmart');
INSERT INTO services
(id, name)
VALUES
(1, 'Service A');
INSERT INTO services
(id, name)
VALUES
(2, 'Service B');
INSERT INTO services
(id, name)
VALUES
(3, 'Service C');
INSERT INTO services
(id, name)
VALUES
(4, 'Service D');
INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(1, 1, 2);
INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(1, 1, 4);
INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(1, 2, 1);
INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(1, 3, 2);
INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(1, 3, 3);
SELECT name,
CASE WHEN EXISTS(SELECT *
FROM services AS S
JOIN service_client_mappings AS m
ON S.id = m.service_id
WHERE s.name = 'Service A'
AND c.id = m.company_id)
THEN 'X'
ELSE '-'
END AS "Service A",
CASE WHEN EXISTS(SELECT *
FROM services AS S
JOIN service_client_mappings AS m
ON S.id = m.service_id
WHERE s.name = 'Service B'
AND c.id = m.company_id)
THEN 'X'
ELSE '-'
END AS "Service B",
CASE WHEN EXISTS(SELECT *
FROM services AS S
JOIN service_client_mappings AS m
ON S.id = m.service_id
WHERE s.name = 'Service C'
AND c.id = m.company_id)
THEN 'X'
ELSE '-'
END AS "Service C",
CASE WHEN EXISTS(SELECT *
FROM services AS S
JOIN service_client_mappings AS m
ON S.id = m.service_id
WHERE s.name = 'Service D'
AND c.id = m.company_id)
THEN 'X'
ELSE '-'
END AS "Service D"
FROM company AS c;
By the way your adata are incorrect... For INSERT INTO service_client_mappings, you should have :
INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(1, 1, 2);
INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(2, 1, 4);
INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(3, 2, 1);
INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(4, 3, 2);
INSERT INTO service_client_mappings
(id, company_id, service_id)
VALUES
(5, 3, 3);
Result is :
name Service A Service B Service C Service D
-------------- --------- --------- --------- ---------
ACME - X - X
Target X - - -
Walmart - X X -
In addition, to build this with a variable number of services you can use dynamic sql... A first way to do this is :
SELECT CONCAT(
'SELECT name,
CASE WHEN EXISTS(SELECT *
FROM services AS S
JOIN service_client_mappings AS m
ON S.id = m.service_id
WHERE s.name = ''', name, '''
AND c.id = m.company_id)
THEN ''X''
ELSE ''-''
END AS "', name , '",') AS SQL_STRING
FROM services;