Return all possible combinations of values within a single column in SQL -

how return list of possible combinations of values within same single column of database 'x'? example have:

    col 1,     1     2     3     4 

and return list of possible combinations like,

    1,2     1,3     1,4     2,3,     2,4     3,4 

....

you've not said rdbms using or whether want limit combinations 2 elements of set.

here oracle answer using hierarchical queries:

sql fiddle

oracle 11g r2 schema setup:

create table test ( col ) select level dual connect level < 5; 

query 1:

select substr(sys_connect_by_path(col, ','), 2) combination test connect prior col < col 

results:

| combination | |-------------| |           1 | |         1,2 | |       1,2,3 | |     1,2,3,4 | |       1,2,4 | |         1,3 | |       1,3,4 | |         1,4 | |           2 | |         2,3 | |       2,3,4 | |         2,4 | |           3 | |         3,4 | |           4 | 

query 2:

select substr(sys_connect_by_path(col, ','), 2) combination   test  level = 2 connect prior col < col , level <= 2 

results:

| combination | |-------------| |         1,2 | |         1,3 | |         1,4 | |         2,3 | |         2,4 | |         3,4 | 

and sql server version using recursive cte:

sql fiddle

ms sql server 2014 schema setup:

create table test ( col int );  insert test           select 1 union select 2 union select 3 union select 4; 

query 1:

with cte ( combination, curr ) (   select cast( t.col varchar(80) ),          t.col     test t   union   select cast( c.combination + ',' + cast( t.col varchar(1) ) varchar(80) ),          t.col     test t          inner join          cte c          on ( c.curr < t.col ) ) select combination cte 

results:

| combination | |-------------| |           1 | |           2 | |           3 | |           4 | |         3,4 | |         2,3 | |         2,4 | |       2,3,4 | |         1,2 | |         1,3 | |         1,4 | |       1,3,4 | |       1,2,3 | |       1,2,4 | |     1,2,3,4 |