I have two equal sets (say, for instance, [1,2,3]) and want to create a table with all possible combinations so I can fill existing counts later in a new column instead of just counting and doing a GROUP BY.
What I have tried:
CREATE TABLE table1
(
Var1 varchar(254),
Var2 varchar(254)
)
INSERT INTO table1 (Var1) VALUES ('1')
INSERT INTO table1 (Var1) VALUES ('2')
INSERT INTO table1 (Var1) VALUES ('3')
INSERT INTO table1 (Var2) VALUES ('1')
INSERT INTO table1 (Var2) VALUES ('2')
INSERT INTO table1 (Var2) VALUES ('3')
This, however, results in:
| Var1 | Var2 |
|---|---|
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| NULL | 1 |
| NULL | 2 |
| NULL | 3 |
When what I want is something like this:
| Var1 | Var2 |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
How can I do this without having to insert each combination manually?
CodePudding user response：
Using your example table and values:
select a.var1, b.Var2
from table1 a, table1 b
where a.var1 is not null and b.Var2 is not null
order by a.var1, b.Var2
NOTE: you can do this with just a single column
CREATE TABLE table1(Var1 varchar(254))
INSERT INTO table1 (Var1) values ('1')
INSERT INTO table1 (Var1) values ('2')
INSERT INTO table1 (Var1) values ('3')
select a.var1, b.Var1 var2
from table1 a, table1 b
order by a.var1, b.Var1
CodePudding user response：
You can do this with cross apply.
select v1.var1, v2.var2
from table1 v1
cross apply table1 v2
order by v1.var1