Find not possible combinations of rows & columns

user_1221992 0
Viewed 40

Based on SQL Server, I have multiple rows with multiple columns. Each row contains a product-configuration which is different in at least one column. Now I need to know which are the not existing configurations. The three columns are only an example to make it easier to understand.

An example of data:

Product Size Color
Shirt S Red
Shirt S Blue
Shirt S Black
Shirt M Red
Shirt M Blue
Shirt L Yellow

As a result, those configurations are missing:

Product Size Color
Shirt S Yellow
Shirt M Black
Shirt M Yellow
Shirt L Red
Shirt L Blue
Shirt L Black
sql-server sql
2 Answers

You can use cross join to generate all the rows. Then filter out the ones that exist:

select p.product, s.size, c.color
from (select distinct product from t) p cross join
     (select distinct size from t) s cross join
     (select distinct color from t) c left join
     t
     on p.product = t.product and
        s.size = t.size and
        c.color = t.color
where t.product is null;
user_11440350

Gordon's answer doesn't take into account nullable columns, here is one that does:

select p.product, s.size, c.color
from (select distinct product from t) p cross join
     (select distinct size from t) s cross join
     (select distinct color from t) c
except
select t.product, t.size, t.color
from t;

If you have actual product, size and color tables, it will be more performant to use those instead of select distinct... from t

user_148689970
Related