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

into a view

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

CodePudding user response：

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

CodePudding user response：

You want something that does the equivalent of PIVOT: https://www.techonthenet.com/oracle/pivot.php

My pivot cheat sheet for Sql Server 2016. You seem to be using Oracle so your mileage may vary:

SELECT <id_column>, <comma separated list of possible values in the <name_column> (preferably via QUOTENAME)>
FROM (
SELECT <id_column>, <name_column>, <value_column> FROM <source_table>
) t
PIVOT 
(
MAX(<value_column>) /* this can be any aggregate function, but I work with a lot of varchar in these queries so MAX() works well for me */
for <name_column> in (
<comma separated list of possible values in the <name_column> (preferably via QUOTENAME)>
) ) AS pvtTable

Turns into

select m.id AS customer, m.is_active, config_pivot.url, config_pivot.db, 
config_pivot.queue from 
(
select id, [url], [db], [queue]  from 
(
select id, config_item, config_setting from customer_config c
)t
pivot
(
MAX(config_setting) FOR config_item in ([url], [db], [queue])
) pvt
) as config_pivot 
inner join customer_master m on m.id = config_pivot.id
where <some conditions>