create table #n_facts (FactID int , Code char(1))
go
insert into #n_facts values (1 , 'X' )
insert into #n_facts values (1 , 'Y' )
insert into #n_facts values (1 , 'Z' )
insert into #n_facts values (2 , 'A' )
insert into #n_facts values (2 , 'B' )
insert into #n_facts values (2 , 'C' )
insert into #n_facts values (3 , 'E' )
insert into #n_facts values (3 , 'F' )
insert into #n_facts values (3 , 'G' )
insert into #n_facts values (4 , 'X' )
insert into #n_facts values (4 , 'Y' )
insert into #n_facts values (4 , 'Z' )
go
select * from #n_facts
select min(c1.factid) as factid, c1.code, c2.code, c3.code
from
(select t1.factid, t1.code
from #n_facts t1
where (select count(*) from #n_facts t2
where t2.factid = t1.factid and t2.code <= t1.code) = 1) c1
join
(select t1.factid, t1.code
from #n_facts t1
where (select count(*) from #n_facts t2
where t2.factid = t1.factid and t2.code <= t1.code) = 2) c2
join
(select t1.factid, t1.code
from #n_facts t1
where (select count(*) from #n_facts t2
where t2.factid = t1.factid and t2.code <= t1.code) = 3) c3
on c3.factid = c2.factid
on c2.factid = c1.factid
group by c1.code, c2.code, c3.code
order by 1 |