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

Question

How do I return a list of all possible combinations of values ​​from a single database column "x"? For example, I have:

    col 1,
    1
    2
    3
    4

And I would like to return a list of all possible combinations, such as

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

....

Answer 1

You did not say which RDBMS you are using or you want to restrict combinations to only two elements of the set.

Here is Oracle's answer using hierarchical queries:

SQL Fiddle

Setting up the Oracle 11g R2 schema :

CREATE TABLE TEST ( COL ) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5;

Request 1 :

SELECT SUBSTR(SYS_CONNECT_BY_PATH(COL, ','), 2) AS combination
FROM TEST
CONNECT BY 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 |

Request 2 :

SELECT SUBSTR(SYS_CONNECT_BY_PATH(COL, ','), 2) AS combination
FROM   TEST
WHERE  LEVEL = 2
CONNECT BY PRIOR COL < COL AND LEVEL <= 2

Results :

| COMBINATION |
|-------------|
|         1,2 |
|         1,3 |
|         1,4 |
|         2,3 |
|         2,4 |
|         3,4 |

And the version of SQL Server using recursive CTE:

SQL Fiddle

Setting up the MS SQL Server 2014 schema :

CREATE TABLE TEST ( COL INT );

INSERT INTO TEST
          SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4;

Request 1 :

WITH cte ( combination, curr ) AS (
  SELECT CAST( t.COL AS VARCHAR(80) ),
         t.COL
  FROM   TEST t
  UNION ALL
  SELECT CAST( c.combination + ',' + CAST( t.col AS VARCHAR(1) ) AS VARCHAR(80) ),
         t.COL
  FROM   TEST t
         INNER JOIN
         cte c
         ON ( c.curr < t.COL )
)
SELECT combination FROM 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 |

Answer 2

select concat(t1.id, ",", t2.id) from t t1 join t t2 on t1.id < t2.id;

SQL Fiddle .

concat(t1.id, ",", t2.id)
-------------------------
1,2
1,3
1,4
2,4
2,3
3,4