In PostgreSQL I have a table like this:

CREATE TABLE cross_table (brand varchar(10), gender varchar(10), sales int);

INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 20);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Adidas', 'Woman', 20);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Adidas', 'Woman', 30);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Puma', 'Woman', 40);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Puma', 'Male', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 20);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Puma', 'Woman', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Adidas', 'Woman', 20);

Then I can run this query to get brand as rows, gender as columns and "Sum of Sales" and "Count of Sales" as values:

SELECT  brand,
        jsonb_object_agg(
            gender,
            json_build_object(
                'Sum of Sales', "Sum of Sales",
                'Count of Sales', "Count of Sales"
            )
        ) AS gender,
        SUM("Sum of Sales")  AS "Sum of Sales",
        SUM("Count of Sales")  AS "Count of Sales"
FROM (
    SELECT  brand,
            gender,
            sum(sales) AS "Sum of Sales",
            count(sales) AS "Count of Sales"
    FROM cross_table
    GROUP BY ROLLUP(brand), gender
) AS a
GROUP BY brand

This would produce the following result:

But I would like to have the values as rows like this:

I've looked at two approaches:

1. Putting the values into an object like this:

json_build_object(
  'Sum of Sales', SUM(sales),
  'Count of Sales', COUNT(sales)
) as "Values"

And then in some way "expanding" them as rows under "Values" column.

2. Using unnest in some way but unsure about that approach.

Important requirement: The values in brand and gender are unknown so the values from brand (nike, etc) and gender (male, etc) are generated dynamically.

Does anyone know how to do this?

For context; this is a continuation of this question:

Get Row Totals for Dynamically Created Pivoted Table

CodePudding user response：

1. Splitting the resulting rows in 2 subrows in not a big deal :

SELECT  brand,
        label,
        jsonb_object_agg(
            gender,
            json_build_object(label, total)
        ) AS gender,
        SUM(total)  AS "Total"
FROM (
    SELECT  brand,
            gender,
            sum(sales) :: integer AS total,
            'Sum of Sales' AS label
    FROM cross_table
    GROUP BY ROLLUP(brand), gender
    UNION ALL
    SELECT  brand,
            gender,
            count(sales) :: integer,
            'Count of Sales'
    FROM cross_table
    GROUP BY ROLLUP(brand), gender
) AS a
GROUP BY brand, label
ORDER BY brand, label ;

2. Splitting the resulting columns in 2 or more subcolumns may be more touchy :

2.A Static list of gender values

If gender has only two values "male" and "woman" then :

CREATE TYPE gender AS (male integer, woman integer) ;

SELECT  brand,
        label,
        (jsonb_populate_record(null :: gender, jsonb_object_agg(lower(gender),total))).*,
        SUM(total)  AS "Total"
FROM (
    SELECT  brand,
            gender,
            sum(sales) :: integer AS total,
            'Sum of Sales' AS label
    FROM cross_table
    GROUP BY ROLLUP(brand), gender
    UNION ALL
    SELECT  brand,
            gender,
            count(sales) :: integer,
            'Count of Sales'
    FROM cross_table
    GROUP BY ROLLUP(brand), gender
) AS a
GROUP BY brand, label
ORDER BY brand, label ;

2.B Dynamic list of gender values

If the gender list of values may vary in time, then :

We need to dynamically create and update the composite type gender from a trigger on table cross_table :

CREATE OR REPLACE FUNCTION trigger_cross_table ()
  RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
  _columns text ;
BEGIN
  SELECT string_agg(DISTINCT gender || ' integer', ',')
    INTO _columns
    FROM cross_table ;
  DROP TYPE IF EXISTS gender ;
  EXECUTE 'CREATE TYPE gender (' || _columns || '(' ;
  RETURN NULL ;
END ; $$ ;

CREATE OR REPLACE TRIGGER trigger_cross_table AFTER INSERT OR UPDATE OF gender OR DELETE
  ON cross_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_cross_table() ;

The final query is the same :

SELECT  brand,
        label,
        (jsonb_populate_record(null :: gender, jsonb_object_agg(lower(gender),total))).*,
        SUM(total)  AS "Total"
FROM (
    SELECT  brand,
            gender,
            sum(sales) :: integer AS total,
            'Sum of Sales' AS label
    FROM cross_table
    GROUP BY ROLLUP(brand), gender
    UNION ALL
    SELECT  brand,
            gender,
            count(sales) :: integer,
            'Count of Sales'
    FROM cross_table
    GROUP BY ROLLUP(brand), gender
) AS a
GROUP BY brand, label
ORDER BY brand, label ;

see dbfiddle