SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Denormalizing Categorizaton on Fly
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/01/2002 :  08:27:36  Show Profile  Visit AskSQLTeam's Homepage
Paul writes "I have an example database that contains 3 tables

* Document
- ID
- Title
- Text

* Category
- ID
- Name

* DocumentCategory
- DocumentID
- CategoryID

How can I turn this normalized structure into a denormalized structure on the fly?

What I need to create is a single Category Field in the result set, that contains all the Category Names associated with that Document in a commma separated list.

Is this even possible?

We currently are using SQL 7.0, Win2k Server SP 2.

Thanks"

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 08/01/2002 :  08:44:38  Show Profile  Visit Merkin's Homepage
Hi

Yes this is possible. Have a read of this http://www.sqlteam.com/item.asp?ItemID=2368 should answer your questions



Damian
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 08/01/2002 :  08:53:53  Show Profile
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}
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000