antonms
asked on
Creating Combinations
Hi
How can SQL be used to create all possible combinations for a data set containing (A,B,C)? The result I require is: (A),(B),(C),(A,B),(A,C),(B
Thanks
How can SQL be used to create all possible combinations for a data set containing (A,B,C)? The result I require is: (A),(B),(C),(A,B),(A,C),(B
Thanks
dsacker
Are these three columns from three different tables, or are they values from one column that you wish to be spread across?
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hmmss... maybe this help:
with
a as (select 'A' p union select 'B' union select 'C'),
b as (select 'A' p union select 'B' union select 'C' union select null),
c as (select 'A' p union select 'B' union select 'C' union select null)
select a.p,b.p, c.p
from a left join b on (b.p>a.p or b.p is null) left join c on (c.p>b.p or c.p is null)
p p p
A NULL NULL
A B NULL
A B C
A C NULL
B NULL NULL
B C NULL
C NULL NULLASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys for the super fast replies!
I'll need to mull over you suggestions and get right back.
I'll need to mull over you suggestions and get right back.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"CROSS JOIN" solution posted by lludden gives 4x4x4=64 result...
just a note that my suggestion will dynamically return as many rows as you have in the base set, so if you have 1 or 100 items, it will work ....
ASKER
I should have mentioned that the solution has to work on SQL Server 2000 SP4 onwards. So I need to also think about how to insert the results in to a table - possibly via pivot - I'll update on Monday - many thanks to all.
if it needs to be sql 2000, you indeed need to first create a table that has as many columns as you have values to start with.
and then, for each number of "columns" to be returned, build the sql to "cross join" as needed to get the results...
and then, for each number of "columns" to be returned, build the sql to "cross join" as needed to get the results...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks chaps - how could these approaches be developed into a procedure were the procedure was provided with a list of the elements. each element in the list would be unique but the number of elements was unknown - I know this is a big ask but I'm struggling to find a solution.
First of all, keep in mind that TSQL is primarily a language for working with sets of data on a well defined table. This is a more analytical type of problem that would be better suited to another language. There are some good examples such as http://www.codeproject.com/KB/recipes/Combinatorics.aspx or http://www2.sas.com/proceedings/sugi23/Posters/p177.pdf
It can be done in SQL. You will have to either accept that each line returned is a single field treated as a delimited list, or define a max list size and accept null values for smaller than max list size.
It can be done in SQL. You will have to either accept that each line returned is a single field treated as a delimited list, or define a max list size and accept null values for smaller than max list size.
ASKER
Perms, Combination and Variation operators would be a great addition to T SQL.