So I got a View of items in Postgres that looks like this:

I need all DISTINCT possible combinations (excluding item_id) but forming groups that not always will include all columns.

Example of combinations for the First Row (but I need the same for all items without getting duplicated groups):

I tried a CROSS JOIN with the original categories tables but I get a enormous table of combinations and also I don't get the groups of 1, 2, 3, 4 or 5 categories, I get the combinations including ALL six categories

CodePudding user response：

You can use CUBE subclause for GROUP BY, which produces the following output for your first row:

    select 
100 brand_id,   101 category_id,    100 color_id,   
101 material_id,    null pattern_id,    101 style_id
    group by
    cube (brand_id, category_id,    color_id,   material_id ,pattern_id ,style_id);

View on DB Fiddle