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

And then I run this query to get brand as rows, gender as columns ans sales as value:

with main_query as (    
    SELECT  brand,
            GROUPING(brand) AS "brand_grouping",
            gender,
            GROUPING(gender) AS "gender_grouping",
            sum(sales) AS "sales"
    FROM    cross_table
    GROUP BY ROLLUP (brand, gender)
),  
second_query AS (
    SELECT  brand,
            brand_grouping,
            cast(
                json_object_agg(
                  gender,
                  sales
                  ORDER BY gender DESC
                ) FILTER (WHERE gender_grouping = 0) AS jsonb) "gender",
            SUM(sales)  AS "sales"
    FROM main_query
    GROUP BY (brand, brand_grouping)
)

SELECT  brand,
        gender,
        sales
FROM    second_query
ORDER BY brand_grouping, brand

This would produce the following result:

Please note: gender column is now in object but brackets won't show in the table view here on Stackoverflow.

This is fine, only problem is that it is missing row totals for the pivoted "gender" column. I can solve this hardcoded by changing last query to this:

SELECT  brand,
        CASE WHEN gender IS null THEN
            jsonb_build_object(
                'Woman', SUM(("gender"->>'Woman')::float8) OVER (),
                'Male', SUM(("gender"->>'Male')::float8) OVER ()
        ) ELSE "gender" END AS "gender",
        sales
FROM    second_query
ORDER BY brand_grouping, brand

Getting this result:

Which is correct but I need to do this dynamically without knowing the keys (Male/Woman) of "gender".

Does anyone know how to do this?

CodePudding user response：

Try this :

SELECT brand
     , jsonb_object_agg(gender, sales) AS gender
     , sum(sales) AS sales
  FROM (
SELECT brand
     , gender
     , sum(sales) AS sales
  FROM cross_table
 GROUP BY ROLLUP(brand), gender
) AS a
GROUP BY brand

Result :

see dbfiddle