The article will give you an iterative method for putting together one list at a time. Here is a set based solution that should do the whole thing at once . . . hopefully much faster.
(Warning! untested code ahead. Had you typed out the ddl and dml to insert sample data, I would have tested it.)
select
d.id,
c.name,
space(8000) as catlist
into
#temp
from
document d
inner join documentcategory dc
on d.id = dc.documentid
inner join category c
on dc.categoryid = c.id
order by
d.id desc
declare @lastid int, @catlist varchar(20)
select @lastid = -1, @lastcat = ''
update
#temp
set
@catlist = catlist = case
when id <> @lastid then name
else @catlist + ',' + name
end,
@lastid = id
select
id,
max(catlist) as catlist
from
#temp
group by
id
drop table #temp
Jay White
{0}
Denormalizing Categorizaton on Fly
Forum Locked
Posted - 08/01/2002 : 08:27:36
