SQL: Converting CROSS JOIN Permutations from Columns to Rows

Data and Databases
1

I need a method to cross join bitmask columns into row combinations while keeping each mask’s allowed values (mask_a: 0/1, mask_b: 0/3, mask_c: 0/5). How?

-- Sample input table
WITH sample_data AS (
  SELECT 0 AS ma, 0 AS mb, 0 AS mc UNION ALL
  SELECT 1, 0, 0 UNION ALL
  SELECT 0, 3, 0 UNION ALL
  SELECT 0, 0, 5
)
-- Desired approach to create row permutations
SELECT * FROM sample_data;
2

A viable approach is to break your mask values into separate derived tables and then use a CROSS JOIN to generate all possible combinations. In my experience, constructing a set or temporary table for each mask value, then applying the join works well. This implementation not only retains clarity but also scales to more complex mask rules. The final step of merging these derived values into a single result produces all combinations as distinct rows, which makes further filtering straightforward if only valid combinations are required.