Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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.1 seconds. Powered By: Snitz Forums 2000