denormalize data in a temporary table

[Hans Bampel]

hello group,

i want to denormalize data in a temporary table for a second
business-system which cant read related datas

what i have:
table Partner: (Id_Partner, NamePartner, ...)
table PartnerProduct: (Id_PartnerProduct, NamePartnerProduct, ...)
table Partner2PartnerProduct: (Id, Id_Partner, Id_PartnerProduct)

Partner to PartnerProduct ist n:m relation (thats why i need
Partner2PartnerProduct)

and the temporary table
TempPartner: (Id_Partner, NamePartnerProducts)
whereas NamePartnerProducts should contains a comma separated list of
the PartnerProduct-Names

what i need:
a trigger or combination of trigger and SP which
operate like the following:
if on Partner2PartnerProduct is a Insert or Update made, then Delete
the row in TempPartner which has the Id of the Partner, and then
create a new row which contains all n:m joined PartnerProduct-Names in
it.

i hope the intention and problem are clear.

thanx for answers,
hans

[Simon Hayes]

"Hans Bampel" <we**********@gmx.de> wrote in message
news:73**************************@posting.google.c om...

hello group,

i want to denormalize data in a temporary table for a second
business-system which cant read related datas

what i have:
table Partner: (Id_Partner, NamePartner, ...)
table PartnerProduct: (Id_PartnerProduct, NamePartnerProduct, ...)
table Partner2PartnerProduct: (Id, Id_Partner, Id_PartnerProduct)

Partner to PartnerProduct ist n:m relation (thats why i need
Partner2PartnerProduct)

and the temporary table
TempPartner: (Id_Partner, NamePartnerProducts)
whereas NamePartnerProducts should contains a comma separated list of
the PartnerProduct-Names

what i need:
a trigger or combination of trigger and SP which
operate like the following:
if on Partner2PartnerProduct is a Insert or Update made, then Delete
the row in TempPartner which has the Id of the Partner, and then
create a new row which contains all n:m joined PartnerProduct-Names in
it.

i hope the intention and problem are clear.

thanx for answers,
hans

The usual answer is that you should handle this in a client tool, rather
than in the database itself. There are ways to do it, but the only really
reliable way is a cursor (and if you need the comma-separated list in an
order, it's probably the only way), and cursors are slow:

http://www.aspfaq.com/show.asp?id=2279

Simon