Generate data combinations in sql

Question

I have a table like below -

COL1	COL2
101	A
102	B
102	C
102	D
103	C
103	E

I need to generate all possible combinations along with uniqueID for a set of unique values in COL1 shown as below - There are 3 unique values in COL1, 6 combinations are possible and so 18 rows should be in the result.

1,101,A
1,102,B
1,103,C
2,101,A
2,102,B
2,103,E
3,101,A
3,102,C
3,103,C
4,101,A
4,102,C
4,103,E
5,101,A
5,102,D
5,103,C
6,101,A
6,102,D
6,103,E

Please help and suggest an answer. I tried using LAG,LEAD, CROSS JOIN, unable to get to a solution.

Answer can be using any of HANA SQL Script or Oracle SQL or MS-SQL. I have a solution that works for fixed number of unique values in COL1, however I need the solution to be dynamic and should work with any number of combinations and values.

*"I tried using `LAG`,`LEAD`, `CROSS JOIN`, unable to get to a solution."* And what *were* those attempts? Why didn't they work? — Thom A, Dec 15 '20 at 15:23
How these combinations are generated ? `1,101,A 1,102,B 1,103,C` — Venkataraman R, Dec 15 '20 at 15:27
@VenkataramanR - I need a SQL code to generate those combinations. That is the help I am asking for. — Gowthi, Dec 15 '20 at 15:27
my doubt is, why only 102,B is considered and not 102,C or 102,D ? — Venkataraman R, Dec 15 '20 at 15:28
@VenkataramanR - they are considered. See the last two combinations. ABC, ABE, ACC, ACE, ADC, ADE — Gowthi, Dec 15 '20 at 15:29
We need to generate ids for every unique combination. That's how we have 1,2,3 — Gowthi, Dec 15 '20 at 18:54
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/225998/discussion-between-gowthi-and-venkataraman-r). — Gowthi, Dec 15 '20 at 18:54
@larnu - I tried Cartesian product and by using lead to generate '1' when prev COL1 == current COL1 etc. But it doesn't give me correct output — Gowthi, Dec 15 '20 at 18:57

Gordon Linoff · Accepted Answer · 2020-12-15T19:15:39.463

1

This answers the original version of the question.

If I understand correctly, I think the simplest method is to put the values on a single row:

select row_number() over (order by t1.col1, t2.col1, t3.col1),
       t1.col1, t1.col2,
       t2.col1, t2.col2,
       t3.col1, t3.col2
from t t1 join
     t t2
     on t1.col1 < t2.col1 join
     t t3
     on t2.col1 < t3.col1;

You can unpivot these results if you like.

edited Dec 15 '20 at 19:15

answered Dec 15 '20 at 15:28

Gordon Linoff

1,242,037
58
646
786

A small change worked. select row_number() over (order by t1.col1, t2.col1, t3.col1), t1.col1, t1.col2, t2.col1, t2.col2, t3.col1, t3.col2 from t t1 join t t2 on t1.col1 < t2.col1 join t t3 on t2.col1 < t3.col1; And yes I need to pivot these results. Thanks for the help. – Gowthi Dec 15 '20 at 15:35
Since there are only 3 cols, I am able to pivot using UNION ALL. Thanks, Gordon. – Gowthi Dec 15 '20 at 15:51
Above code helps if I have only 3 unique values in COL1, however I need the solution to be dynamic and should work with any number of combinations and values – Gowthi Dec 15 '20 at 18:31
@Gowthi . . . This answers the question that you asked here. If you have a different question, ask it as a new question. – Gordon Linoff Dec 15 '20 at 18:40
I updated my question @gordon linoff, can you please answer now. Please help,Thanks!! – Gowthi Dec 15 '20 at 18:50

score 0 · Answer 2 · answered Dec 15 '20 at 18:44

0

You can do a cartesian product between the distinct values of the rows

select *
    from (select distinct COL1
          from yourTable) as t1,
         (select distinct COL2
          from yourTable) as t2

answered Dec 15 '20 at 18:44

AirlineDog

520
8
21

No, it won't work like this. The number of combinations are dependent on distinct values in COL1 and the values present in COL2 for each value in COL1. Plain Cartesian product won't solve my case, any other ideas are appreciated, thanks!! – Gowthi Dec 15 '20 at 18:53

Generate data combinations in sql

2 Answers2