drop table #test
go
create table #test ( u char(5), d char(5))
insert #test (u, d) values('UNIT1', 'DEG10')
insert #test (u, d) values('UNIT1', 'DEG11')
insert #test (u, d) values('UNIT1', 'DEG12')
insert #test (u, d) values('UNIT1', 'DEG22')
insert #test (u, d) values('UNIT1', 'DEG24')
declare @d varchar(255)
set @d = ''
update #test
set @d = @d + d + ','
select top 1 u, @d as all_deg from #test where u = 'UNIT1'

select * into tempdb..test from #test
go
Alter view vwtest
as
select u, d ,
        (select count(*) from tempdb..test t2 where t2.d <= t1.d) as cnt
from tempdb..test t1
go

-- Easy group by one
select u, max(case when cnt = 1 then d + ',' else '' end) +
                max(case when cnt = 2 then d + ',' else '' end) + 
                max(case when cnt = 3 then d + ',' else '' end) as all_d
from vwTest
group by u
go

-- Or this big one
select coalesce(t1.u, t2.u, t3.u, t4.u, t5.u) AS u,
        (t1.d + ',' + t2.d + ',' + t3.d + ',' + t4.d + ',' + t5.d) as all_d
from
(select u, d from vwtest where cnt = 1)  as t1
full join 
(select u, d from vwtest where cnt = 2)  as t2
on t1.u = t2.u
full join 
(select u, d from vwtest where cnt = 3)  as t3
on t1.u = t3.u
full join 
(select u, d from vwtest where cnt = 4)  as t4
on t1.u = t4.u
full join 
(select u, d from vwtest where cnt = 5)  as t5
on t1.u = t5.u
go

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