How to create a SQL view by consolidating a table grouped by the values of their columns

Question

I don't know how to call exactly what I'm trying to do, I'm picking up an existing project, but their views are unalterable, so I'm attempting to regenerate them by updating their config, unfortunately the initial db is slimmed down into only having 3 columns

customer	config_item	config_setting
10000001	url	http://localhost
10000001	db	http://localhost:5432
10000001	queue	http://localhost
10000002	url	http://localhost
10000002	db	http://localhost:5432
10000002	queue	http://localhost

into a view

customer_id	url	db	queue
10000001	http://localhost	http://localhost:5432	http://localhost
10000002	http://localhost	http://localhost:5432	http://localhost

CREATE OR REPLACE VIEW customer_settings AS (
  SELECT m.id AS customer, m.is_active FROM
    customer_master m
  LEFT JOIN 
    customer_config c
    c.url = c.config_setting WHERE c.customer_id = m.id AND c.config_item = 'url'
    c.db = c.config_setting WHERE c.customer_id = m.id AND c.config_item = 'db'
    c.queue = c.config_setting WHERE c.customer_id = m.id AND c.config_item = 'queue'
);

I can get all of the necessary data through individual selects, but I don't know how to implement those into a view

select config_setting from customer_config where customer_id = 1000001 AND config_item = 'url';

select config_setting from customer_config where customer_id = 1000001 AND config_item = 'db';

select config_setting from customer_config where customer_id = 1000001 AND config_item = 'queue';

It's not exactly clear what you are asking ... – Erwin Brandstetter Feb 15 '22 at 03:27 — Erwin Brandstetter, Feb 15 '22 at 03:27

score 0 · Answer 1 · answered Feb 15 '22 at 03:24

crosstab() is typically fastest. Minimal form:

SELECT customer_id, url, db, queue
FROM   crosstab(
  'SELECT customer_id, config_item, config_setting
   FROM   customer_config
   ORDER  BY 1, 2'
   ) AS (customer_id int, db text, queue text, url text);

db<>fiddle here

Your question leaves room for interpretation. You may need to be more explicit, especially if rows can be missing in customer_config. See:

PostgreSQL Crosstab Query

How to create a SQL view by consolidating a table grouped by the values of their columns

1 Answers1