Dynamic columns in PostgreSQL query

Question

In my PostgreSQL database I have the following schema:

CREATE TABLE clients (
    id integer NOT NULL,
    name character varying(255)
);

CREATE TABLE services (
    id integer NOT NULL,
    name character varying(255) NOT NULL
);

CREATE TABLE client_services (
    id integer NOT NULL,
    client_id integer NOT NULL,
    service_id integer NOT NULL
);


INSERT INTO clients(id, name)
VALUES (1, 'Client 1'), 
       (2, 'Client 2');

INSERT INTO services(id, name)
VALUES (1, 'Service 1'), 
       (2, 'Service 2');       

INSERT INTO client_services(id, client_id, service_id)
VALUES (1, 1, 1),
       (2, 1, 2),
       (3, 2, 2);

and I have the following query:

SELECT DISTINCT
    c.name,
    COUNT(cs.id) FILTER(WHERE s.name = 'Service 1') AS "Service 1",
    COUNT(cs.id) FILTER(WHERE s.name = 'Service 2') AS "Service 2"
FROM
    clients c
INNER JOIN
    client_services cs ON cs.client_id = c.id
INNER JOIN 
    services s ON s.id = cs.service_id
GROUP BY
    c.name;

which returns this:

| name     | Service 1 | Service 2 |
| -------- | --------- | --------- |
| Client 1 | 1         | 1         |
| Client 2 | 0         | 1         |

Problem is that if I decide to add another service to services table I will need to update my query. Is there any way to make that part of the query dynamic:

COUNT(cs.id) FILTER(WHERE s.name = 'Service 1') AS "Service 1",
COUNT(cs.id) FILTER(WHERE s.name = 'Service 2') AS "Service 2"

EDIT:

I forgot to add link do sql fiddle:

https://www.db-fiddle.com/f/mhcs7UBpW93fHoYqadNZMx/1

You should do that kind of formatting in your application, SQL isn't really good at that. How (where) exactly are you using the result of that query? If you are using that for display purposes, aggregating into a key/value structure (e.g. JSONB) might be a better option — , Mar 25 '19 at 07:22
Where is the end result supposed to be displayed? Only query or in an application (web or other)? Why not simply do something like: SELECT DISTINCT c.id AS client_id, c.name AS client_name, s.id AS service_id, s.name AS service_name, count(*) AS client_service_count FROM clients c JOIN client_services cs ON cs.client_id = c.id JOIN services s ON s.id = cs.service_id GROUP BY c.id, s.id ; And then use the result in your display table? — Michael Muryn, Mar 25 '19 at 07:33
If you want to have explicit 0 for client_service_count, then you will need to do a INNER JOIN services and LEFT JOIN client_services. — Michael Muryn, Mar 25 '19 at 07:33
This query will be used for display purposes in my web application. — Mateusz Urbański, Mar 25 '19 at 07:45
What you are trying to do is really a pivot table with dynamic columns. which is very difficult in SQL. As stated by others, if you are displaying this in a web application, a better approach is to do this logic on the application side in your code. For reference, here is a question regarding dynamic pivot tables: https://stackoverflow.com/questions/28087948/dynamic-pivot-query-using-postgresql-9-3 — patrick3853, Mar 25 '19 at 07:48
If you want to display the result in a web application, then a JSON aggregate is the more flexible way to do it in SQL. — , Mar 25 '19 at 07:54

Dynamic columns in PostgreSQL query

0 Answers0