Postgres Query to Generate a Table Matrix

Question

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);

You can use CASE statement to do so. Please provide DDL and a set of INSERTs — SQLpro
– SQLpro, Commented Nov 29, 2022 at 15:10
@SQLpro - Please see the update with DDL/INSERTs. Are those helpful? — KevinL
– KevinL, Commented Nov 30, 2022 at 16:01

SQLpro · Accepted Answer · 2022-12-02 07:50:45Z

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;

Thanks @SQLpro! This worked like a charm! And thanks for the sanity check on the INSERTS. I was trying to generate something on the fly for you and it was my first time. ;) Question, is there a way to build this in such a way that it would automatically expand to handle new "services" if "Service E" was added or if "Service A" was removed?
Not directly... SQL needs a static structure for SQL identifier (aka table name, column name...). In this case you can use dynamic SQL that is to build the query by concatening strings... AS a begining of an example, see the post I have add...

Collectives™ on Stack Overflow

Postgres Query to Generate a Table Matrix

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related