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

This page was last updated on May 01, 2006 04:28 PM.