#Postgresql question - let's see if the Fediverse can help. Here's hoping.
If I have rows representing two sets of data in one list, e.g.
A1
A2
B1
B2
how would I get all combinations of A group items with Bs? I.e.
A1,B1
A1,B2
A2,B1
A2,B2
Would you use the CUBE function (which I've not used before)?
Now what about if you had an arbitrary number of groups, e.g. for three
A1
A2
B1
C1
C2
to give
A1,B1,C1
A1,B1,C2
A2,B1,C1
A2,B1,C2
without rewriting the query depending on the number of groups. Something recursive? Or again, would CUBE work?
The simple case is just a cross join of your A and B groups, eg:
SELECT * FROM
(SELECT e FROM l WHERE e LIKE 'A%') a
CROSS JOIN
(SELECT e FROM l WHERE e LIKE 'B%') b;
For the more complex case I think all approaches you try will run into the output columns need to be known and can't be dynamically materialised, ie with crosstab.
Likely you can do it in a function which computes the groups, builds a dynamic query and returns JSON.
@intrbiz yes I was worried about the varying number of output columns but have decided it’s ok to generate a one column CSV. I can always parse or split them out again if necessary. In the end I’ve done it in-app recursively adding on each option to a growing set of CSVs. I have proved it’s possible to do in SQL with a recursive CTE (with a bit of help from AI I admit), that might be a future enhancement. Thanks, I’ll come back to this is I look at it again.