Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

How do I return a list of all possible combinations of values from within the same single column of database 'x'? For example I have:

    col 1,
    1
    2
    3
    4

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

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

....

like image 220
steve zissou Avatar asked Jun 26 '15 10:06

steve zissou


People also ask

How do I get unique combinations in SQL?

Simply use the DISTINCT keyword: SELECT DISTINCT Latitude, Longitude FROM Coordinates; This will return values where the (Latitude, Longitude) combination is unique.

Can you have multiple values in one column in SQL?

For storing multiple values in single column, you can have json or jsonb column in your table, so that you can store multiple values as json array in column.

How to have the combination set in SQL query?

By applying WHERE clause with suitable criteria, SQL programmers can have the combination set easily in this SQL query. Now the result set returns "7 choose 3" for combination of 3 colors out of 7 possible without repetition.

Is there a way to return both columns from one row?

This solution returns both columns from all rows, but not all combinations of both columns. This is not a generic solution, but a specialized one that works only when the data is arranged in a certain way. This uses 2 cte's, the first simply reproduces your input table, the second turns both columns into a single column.

How to join two columns in a table with different cols?

;with cteAllColumns as ( select col1 as col from YourTable union select col2 as col from YourTable ) select c1.col, c2.col from cteAllColumns c1 cross join cteAllColumns c2 where c1.col < c2.col order by c1.col, c2.col You could cartesian join the table to itself, which would return all combinations of both columns.

How to join a table with itself to get possible combinations?

This example joins a table with itself to get all the possible combinations of the values in a column. Declare the PROCLIB library. The PROCLIB library is used in these examples to store created tables. Create the FLIGHTS table. The CREATE TABLE statement creates the table FLIGHTS from the output of the query.


1 Answers

You've not said which RDBMS you are using or whether you want to limit the combinations to just 2 elements of the set.

Here is an Oracle answer using hierarchical queries:

SQL Fiddle

Oracle 11g R2 Schema Setup:

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

Query 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 |

Query 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 an SQL Server version using a recursive CTE:

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE TEST ( COL INT );

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

Query 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 |
like image 135
MT0 Avatar answered Oct 13 '22 12:10

MT0