If Col1
is sorted, as in the example, it looks like this for SQL Server:
CREATE TABLE #tmp
(
ID INT,
Col2 INT,
Col1 Varchar(1)
)
insert into #tmp (ID, Col2, Col1)
Values
(11, 1, 'A'),
(12, 2, 'D'),
(13, 3, 'G'),
(14, 1, 'B'),
(15, 2, 'E'),
(16, 3, 'H'),
(17, 1, 'C'),
(18, 2, 'F'),
(19, 3, 'I')
select distinct tmp1.Col2,
tmp1.Col1,
tmp2.Col1,
tmp3.Col1
from #tmp tmp1
inner join #tmp tmp2
on tmp1.Col2 = tmp2.Col2
and tmp1.Col1 < tmp2.Col1
inner join #tmp tmp3
on tmp1.Col2 = tmp3.Col2
and tmp1.Col1 < tmp3.Col1
and tmp2.Col1 < tmp3.Col1
order by tmp1.Col2