Return all possible combinations of values within a single column in SQL
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 |
Return all possible combinations of values within a single column in MySQL
You can use recursive common table expression:
with recursive cte (n) AS
(
select distinct cast(u.grade as char(255)) from users u
union all
select concat(u.grade, c.n)
from users u
join cte c on u.grade != c.n
where position(u.grade in c.n)=0
and u.grade < c.n
)
select c.n, count(*)
from cte c
join users u on position(u.grade in c.n)
group by c.n
See DBFiddle
How to return all unique combinations from 1 column in sql?
Use a self-join:
SELECT
t1.col,
t2.col
FROM yourTable t1
INNER JOIN yourTable t2
ON t1.col <= t2.col
ORDER BY
t1.col,
t2.col;
Demo
SQL - Return All Possible Combinations of Values in a Column by Means of Two New Columns
The idea is to enumerate the power set, by assigning each value a power of 2, then iterate from 1 to 2^n - 1 , and filter the elements which corresponding bit is set.
-- map each value with a power of 2 : 1, 2, 4, 8, 16
with recursive ELEMENTS(IDX, POW, VAL) as (
-- init with dummy values
values(-1, 0.5, null)
union all
select IDX + 1,
POW * 2,
-- index the ordered values from 0 to N - 1
( select COL0
from DATA d1
where (select count(*) from DATA d2 where d2.COL0 < d1.COL0) = IDX + 1)
from ELEMENTS
where IDX + 1 < (select count(*) from data)
), POWER_SETS(ITER, VAL, POW) as (
select 1, VAL, POW from ELEMENTS where VAL is not null
union all
select ITER + 1, VAL, POW
from POWER_SETS
where ITER < (select SUM(POW) from elements) )
select ITER, VAL from POWER_SETS
-- only if the value's bit is set
where ITER & POW != 0
EDIT: 2nd version, with help from MatBailie. Only one of the CTE is recursive, and singleton subsets are excluded.
WITH RECURSIVE
-- number the values
elements(val, idx) AS (
SELECT d1.col0, (select count(*) from DATA d2 where d2.COL0 < d1.COL0)
FROM DATA d1
),
-- iterate from 3 (1 and 2 are singletons)
-- to 2^n - 1 (subset containing all the elements)
subsets(iter) AS (
VALUES(3)
UNION ALL
SELECT iter + 1
from subsets
WHERE iter < (1 << (SELECT COUNT(*) FROM elements)) - 1
)
SELECT iter AS Col1, val AS Col2
FROM elements
CROSS JOIN subsets
-- the element is present is this subset (the bit is set)
WHERE iter & (1 << idx) != 0
-- exclude singletons (another idea from MatBailie)
AND iter != (iter & -iter)
ORDER BY iter, val
Return all possible combinations of values on columns in SQL
Assuming at least SQL 2005 for the CTE:
;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
Get all the possible combinations of one column
You can use recursive cte to get the desired results and as @Serg suggested, I think 'B' and 'C' should also be the part of the final result.
create table test(col varchar(10))
insert into test
select 'A'
union select 'B'
union select 'C'
;WITH cte (grp, col)
AS
(
SELECT CAST(t.COL AS VARCHAR(100)), t.COL
FROM TEST t
UNION ALL
SELECT CAST(c.grp + ',' + t.col AS VARCHAR(100)), t.COL
FROM TEST t
INNER JOIN cte c
ON c.col < t.COL
)
SELECT grp FROM cte;
Please see the db<>fiddle here.
All possible combinations of data in a single table in alphabetical order
This should work for any table size.
Result here
Note: this requires MySQL 8+.
-- TABLE
CREATE TABLE IF NOT EXISTS `fruits`
(
`id` int(6) NOT NULL,
`fruit` char(20)
);
INSERT INTO `fruits` VALUES (1, 'apple');
INSERT INTO `fruits` VALUES (2, 'orange');
INSERT INTO `fruits` VALUES (3, 'pear');
INSERT INTO `fruits` VALUES (4 ,'plum');
-- QUERY
WITH RECURSIVE cte ( combination, curr ) AS (
SELECT
CAST(t.fruit AS CHAR(80)),
t.id
FROM
fruits t
UNION ALL
SELECT
CONCAT(c.combination, ', ', CAST( t.fruit AS CHAR(100))),
t.id
FROM
fruits t
INNER JOIN
cte c
ON (c.curr < t.id)
)
SELECT combination FROM cte;
Credit:
Code adapted from this answer
How to get combination of value from single column?
Below is for BigQuery Standard SQL and answers just the exact question in the title of your post which is:
How to get combination of value from single column?
#standardSQL
CREATE TEMP FUNCTION test(a ARRAY<INT64>)
RETURNS ARRAY<STRING>
LANGUAGE js AS '''
var combine = function(a) {
var fn = function(n, src, got, all) {
if (n == 0) {
if (got.length > 0) {
all[all.length] = got;
} return;
}
for (var j = 0; j < src.length; j++) {
fn(n - 1, src.slice(j + 1), got.concat([src[j]]), all);
} return;
}
var all = [];
for (var i = 1; i < a.length; i++) {
fn(i, a, [], all);
}
all.push(a);
return all;
}
return combine(a)
''';
WITH types AS (
SELECT DISTINCT type, CAST(DENSE_RANK() OVER(ORDER BY type) AS STRING) type_num
FROM `project.dataset.order`
WHERE status = 'OK'
)
SELECT items, STRING_AGG(type ORDER BY type_num) types
FROM UNNEST(test(GENERATE_ARRAY(1,(SELECT COUNT(1) FROM types)))) AS items,
UNNEST(SPLIT(items)) AS pos
JOIN types ON pos = type_num
GROUP BY items
You can test, play with above using sample data from your questions as in below
#standardSQL
CREATE TEMP FUNCTION test(a ARRAY<INT64>)
RETURNS ARRAY<STRING>
LANGUAGE js AS '''
var combine = function(a) {
var fn = function(n, src, got, all) {
if (n == 0) {
if (got.length > 0) {
all[all.length] = got;
} return;
}
for (var j = 0; j < src.length; j++) {
fn(n - 1, src.slice(j + 1), got.concat([src[j]]), all);
} return;
}
var all = [];
for (var i = 1; i < a.length; i++) {
fn(i, a, [], all);
}
all.push(a);
return all;
}
return combine(a)
''';
WITH `project.dataset.order` AS (
SELECT '2019-01-02' dt, 'Shirt' type, 'Cashless' payment, 101 customer_no, 'Cancel' status UNION ALL
SELECT '2019-01-02', 'Jeans', 'Cashless', 133, 'OK' UNION ALL
SELECT '2019-01-02', 'Jeans', 'Cash', 102, 'OK' UNION ALL
SELECT '2019-01-02', 'Cap', 'Cash', 144, 'OK' UNION ALL
SELECT '2019-01-02', 'Shirt', 'Cash', 132, 'OK' UNION ALL
SELECT '2019-01-01', 'Jeans', 'Cash', 111, 'Cancel' UNION ALL
SELECT '2019-01-01', 'Cap', 'Cash', 141, 'OK' UNION ALL
SELECT '2019-01-01', 'Shirt', 'Cash', 101, 'OK' UNION ALL
SELECT '2019-01-01', 'Jeans', 'Cash', 105, 'OK'
), types AS (
SELECT DISTINCT type, CAST(DENSE_RANK() OVER(ORDER BY type) AS STRING) type_num
FROM `project.dataset.order`
WHERE status = 'OK'
)
SELECT items, STRING_AGG(type ORDER BY type_num) types
FROM UNNEST(test(GENERATE_ARRAY(1,(SELECT COUNT(1) FROM types)))) AS items,
UNNEST(SPLIT(items)) AS pos
JOIN types ON pos = type_num
GROUP BY items
with result
Row items types
1 1 Cap
2 2 Jeans
3 3 Shirt
4 1,2 Cap,Jeans
5 1,3 Cap,Shirt
6 2,3 Jeans,Shirt
7 1,2,3 Cap,Jeans,Shirt
Related Topics
Re-Writing "Fuzzy Join" Functions from R to SQL
How to Sort in Order as Entered in SQL Server
Find Referenced Field(S) of Foreign Key Constraint
Sqlite String Contains Other String Query
SQL Server Join Missing Null Values
How to Remove White Space Characters from a String in SQL Server
Sql-Server Performance: What Is Faster, a Stored Procedure or a View
How to Run SQL Statements on a Named Range Within an Excel Sheet
Rodbc Queries Returning Zero Rows
SQL Server: Replace Invalid Xml Characters from a Varchar(Max) Field
Select Rows with Maximum Column Value Group by Another Column
Return a Query from a Function
Where Should I Start with My Opc-Ua Client