DrDizzy
DrDizzy

Reputation: 1

PostgreSQL Dynamic crosstab query

I have a fact table like

CREATE TABLE fact_table (
  customer VARCHAR(50),
  product VARCHAR(50),
  city VARCHAR(50),
  measure INTEGER
);

INSERT INTO fact_table (customer, product, city, measure)
VALUES
  ('Oleg', 'Shampoo', 'Zurich', 5),
  ('Oleg', 'Bread', 'Bern', 1),
  ('Murad', 'Shampoo', 'Bern', 3),
  ('Murad', 'Beer', 'Lausanne', 4),
  ('Olva', 'Beer', 'Lausanne', 2),
  ('Olva', 'Bread', 'Zurich', 2),
  ('Olva', 'Shampoo', 'Bern', 1);
  
  
CREATE EXTENSION IF NOT EXISTS tablefunc;

and I want to dynamically (as I don't have full knowledge of the city names a priori) obtain and output a table like

 customer | bern | lausanne | zurich
----------+------+----------+--------
 Murad    |    3 |        4 |
 Oleg     |    1 |          |      5
 Olva     |    3 |        2 |      2

without using \crosstabview.

Since creating a temporary table and querying it gave the desired output, i.e., using

CREATE  TABLE temp_t AS 
SELECT * 
FROM 
crosstab(
' SELECT customer, city, sum(measure) FROM fact_table 
           GROUP BY customer, city ORDER BY 1,2'
, 
$values$ VALUES ('Bern'), ('Lausanne'), ('Zurich')$values$
) 
AS ct(customer text, bern integer, lausanne integer, zurich integer)
;

I thought about doing that dynamically

DO $$ 
DECLARE
  city_list text;
  city_values text;
  dynamic_query text;
BEGIN
  -- Retrieve the distinct city names from the fact_table and map them into column definitions
  SELECT string_agg(DISTINCT lower(city), ' integer, ')
  INTO city_list
  FROM fact_table;
  
  -- Retrieve the distinct city names from the fact_table
  SELECT string_agg(DISTINCT city, ' ''), ('' ')
  INTO city_values
  FROM fact_table;
  
  -- drop table if already existing
  DROP TABLE IF EXISTS temp_t CASCADE;
  
  -- Generate the dynamic SQL query with the pivoted columns
  dynamic_query := 'CREATE  TABLE temp_t AS 
            SELECT * 
            FROM 
            crosstab(
            $inner$ 
            SELECT customer, city, sum(measure) FROM fact_table 
            GROUP BY customer, city ORDER BY 1,2
            $inner$, 
           $values$ VALUES ('' ' || city_values || ' '') $values$
        ) AS ct(customer text, ' || city_list || ' integer)';
  
  -- Execute the dynamic SQL query
  EXECUTE dynamic_query;
END $$;

However, querying that temporary table yields an empty table

 customer | bern | lausanne | zurich
----------+------+----------+--------
 Murad    |      |          |
 Oleg     |      |          |
 Olva     |      |          |

What's wrong here?

Upvotes: 0

Views: 52

Answers (0)

Related Questions