Get combination over two columns

Nithin 0
Viewed 24

Help me to get SQL

column 1 column 2 Id
DEP-1 1 1
DEP-1 1 2
DEP-1 2 3
DEP-2 3 4
DEP-3 1 5
DEP-3 2 6
DEP-3 3 7
DEP-3 2 8
DEP-3 3 9

I have above table I need to write SQL to display all DISTINCT combination of column 2 over column 1. for example DEP-1 has 1 and 2 in column 2. my final table has to look below.

column 1 column 2 Id column 2 map
DEP-1 1 1 1~2
DEP-1 1 2 1~2
DEP-1 2 3 1~2
DEP-2 3 4 3
DEP-3 1 5 1~2~3
DEP-3 2 6 1~2~3
DEP-3 3 7 1~2~3
DEP-3 2 8 1~2~3
DEP-3 3 9 1~2~3
sql
1 Answers
select origin.*, c2_map
from origin
join (
 select c1, group_concat("~", c2) as c2_map
 from (
   select distinct c1, c2 from origin
 ) t1
 group by c1
) t2
on origin.c1 = t2.c1

Note:

  • group_concat(sep, value) is an aggregate function, it depends on the database you use, it means join the values together using sep
amow0
Related