How do I return a list of all combinations of values in 2 columns so they are new rows in T-SQL?
e.g.
Col1, Col2
---- ----
1 2
1 4
1 5
and turn this into all combinations:
1 2
1 4
1 5
2 4
2 5
4 5
8 Answers
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
1 Comment
LondonRob
As per the comments in @BobBlack's answer, this is the accepted solution because it avoids repeating
1-2 and 2-1 etc. (i.e. the results are an unordered set)You could cartesian join the table to itself, which would return all combinations of both columns.
select
distinct
t1.Col1,
t2.Col2
from
MyTable t1,
MyTable t2
4 Comments
Sir Wobin
Add "select distinct" and you've got it.
jaffa
I've tried this but I need Col 2 values in Col 1 so they are swapped round. I thought unpivot might help but not really sure...
Bob Black
@Jon - are you saying you need Col2 to be displayed first, before Col1, in the resultset? Not quite sure what the issue is.
jaffa
@Sir Wobin: The issue is that I need to return all unique combinations. 1-2 is the same as 2-1 so can be ommitted. The CTE with swapped columns unioned and then cross joined seems to do the trick (see above solution).
You can do a self cross join...
SELECT a.Col1, b.Col2
FROM MyTable a
CROSS JOIN MyTable b
2 Comments
V Maharajh
Why isn't this answer more popular? Is there something wrong?
LondonRob
This needs
SELECT DISTINCT I think. Also, although this is useful it doesn't do what the OP wants which is all combinations of both columns!I was looking for something that would do this using only the SQL available to Microsoft Access 2016. I ended up figuring out something that others may find useful. This code utilizes CROSS JOIN so I found that it is necessary to split the two columns into two separate tables (each with one column). The AND statement forces one column to be less than the other, thereby eliminating any repetitive 1-2, 2-1 sort of occurrences.
SELECT DISTINCT Table1.Column1, Table2.Column1
FROM Table1, Table2
WHERE Table1.Column1 <> Table2.Column1
AND Table2.Column1 < Table1.Column1;
1 Comment
Noël Widmer
Hi Josh, welcome to Stack Overflow! New users often write pretty bad answers. Congratulations for putting in the effort of explaining why your solution might be better suited for some readers. +1 for that!
I think this has been over complicated! Just:
SELECT distinct Col1, Col2
FROM MyTable
to get all possible combinations..
4 Comments
Casual user
This query ran in an instant for me - it returned all possible combinations of two fields in my table. The other queries timed out. I hope it helps others, like you helped me identify my shortcomings.
Steve
This solution is excellent, worked like a charm for me and was very simple. Never feel bashful about adding a potentially better solution to an already answered question.
Bob Black
This solution returns both columns from all rows, but not all combinations of both columns.
Daniel Leiszen
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. The final select crossjoin's this set to itself to produce the required output
with t(c1,c2)
AS
(
select 1,2
union select 1,4
union select 1,5
)
,t2(c)
as
(
select c1 from t
union select c2 from t
)
select t2_1.c, t2_2.c
from t2 t2_1
cross join t2 t2_2
where t2_1.c<t2_2.c
order by t2_1.c
Comments
I find an inner join more intuitive because I use it more frequently than a cross join:
;with cteAllColumns as (
select col1 as col
from YourTable
union
select col2 as col
from YourTable
)
select c1.col, c2.col
from cteAllColumns c1
join cteAllColumns c2 on 1=1
where c1.col < c2.col
order by c1.col, c2.col
Comments
Making Joe Answer easier
declare @t1 table (col1 varchar(5))
insert @t1
select 'A' UNION
select 'B' UNION
select 'C'
declare @t2 table (col2 varchar(5))
insert @t2
select '1' UNION
select '2' UNION
select '3'
;with cteAllColumns as (
select col1 as col
from @t1
union
select col2 as col
from @t2
)
select c1.col, c2.col
from cteAllColumns c1
cross join cteAllColumns c2
where c1.col < c2.col
order by c1.col, c2.col
verify your combinations Qty (No of rows) http://www.calculatorsoup.com/calculators/discretemathematics/combinations.php
new rows? What are the rules for combining? Is1,1valid or not?