How to concatenate multiple rows into one field?

[mitmed]

Hi,

I hope someone here can help me.

We have a product table which has a many-to-many relation
to a category table (joined through a third "ProductCategor y" table):

[product] ---< [productCategory] >--- [category]
--------- ---------------- ----------
productID productCategory ID categoryID
productName productID categoryName
categoryID

We want to get a view where each product occupies just one row, and
any multiple category values are combined into a single value, eg
(concatenating with commas):

Product Category
-------------------
cheese dairy
cheese solid
milk dairy
milk liquid
beer liquid

will become:

Product Category
-------------------
cheese dairy, solid
milk dairy, liquid
beer liquid

What is the best way to do it in SQL?

Thanks and regards,
Dmitri

[Simon Hayes]

Re: How to concatenate multiple rows into one field?


"mitmed" <mitmed@yahoo.c om> wrote in message
news:c2fa9a07.0 408182248.684df d7a@posting.goo gle.com...[color=blue]
> Hi,
>
> I hope someone here can help me.
>
> We have a product table which has a many-to-many relation
> to a category table (joined through a third "ProductCategor y" table):
>
> [product] ---< [productCategory] >--- [category]
> --------- ---------------- ----------
> productID productCategory ID categoryID
> productName productID categoryName
> categoryID
>
> We want to get a view where each product occupies just one row, and
> any multiple category values are combined into a single value, eg
> (concatenating with commas):
>
> Product Category
> -------------------
> cheese dairy
> cheese solid
> milk dairy
> milk liquid
> beer liquid
>
> will become:
>
> Product Category
> -------------------
> cheese dairy, solid
> milk dairy, liquid
> beer liquid
>
> What is the best way to do it in SQL?
>
> Thanks and regards,
> Dmitri[/color]

The usual answer is that you should do this in the client, not in TSQL, but
if you must then the only reliable way is using a cursor.

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

Simon

[mitmed]

Re: How to concatenate multiple rows into one field?

Thanks for your reply Simon,

I completely agree with you that the best place for this type of code
is on the client side. The issue is that my client side is a Crystal
Report (in VB.NET) and i don't know how to do this kind of processing
there. The report i'm trying to produce is the list of products and
their details including categories a product belongs to. I would
really appreciate if somebody could point me to a good crystal report
resource, where it shows how to do things like that if it's possible.

Regards,
Dmitri

"Simon Hayes" <sql@hayes.ch > wrote in message news:<4124eaa5_ 2@news.bluewin. ch>...[color=blue]
> "mitmed" <mitmed@yahoo.c om> wrote in message
> news:c2fa9a07.0 408182248.684df d7a@posting.goo gle.com...[color=green]
> > Hi,
> >
> > I hope someone here can help me.
> >
> > We have a product table which has a many-to-many relation
> > to a category table (joined through a third "ProductCategor y" table):
> >
> > [product] ---< [productCategory] >--- [category]
> > --------- ---------------- ----------
> > productID productCategory ID categoryID
> > productName productID categoryName
> > categoryID
> >
> > We want to get a view where each product occupies just one row, and
> > any multiple category values are combined into a single value, eg
> > (concatenating with commas):
> >
> > Product Category
> > -------------------
> > cheese dairy
> > cheese solid
> > milk dairy
> > milk liquid
> > beer liquid
> >
> > will become:
> >
> > Product Category
> > -------------------
> > cheese dairy, solid
> > milk dairy, liquid
> > beer liquid
> >
> > What is the best way to do it in SQL?
> >
> > Thanks and regards,
> > Dmitri[/color]
>
> The usual answer is that you should do this in the client, not in TSQL, but
> if you must then the only reliable way is using a cursor.
>
> http://www.aspfaq.com/show.asp?id=2279
>
> Simon[/color]

[steve]

Re: How to concatenate multiple rows into one field?

You can do this very easily with the RAC utility/tool for S2k.
No sql coding required.

For info on concatenation over rows see:

http://www.rac4sql.net/onlinehelp.asp?topic=236

RAC v2.2 and QALite @

https://www.rac4sql.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: How to concatenate multiple rows into one field?

mitmed (mitmed@yahoo.c om) writes:[color=blue]
> I completely agree with you that the best place for this type of code
> is on the client side. The issue is that my client side is a Crystal
> Report (in VB.NET) and i don't know how to do this kind of processing
> there. The report i'm trying to produce is the list of products and
> their details including categories a product belongs to. I would
> really appreciate if somebody could point me to a good crystal report
> resource, where it shows how to do things like that if it's possible.[/color]

We use Crystal in our system (and we hate it!), but we never let Crystal
near SQL Server itself. The "database" we tell Crystal about is text files
with all the columns. The actual queries are submitted from VB6, and then
we feed Crystal one of more recordsets, typically augmented with other stuff
that the VB code puts in.

Exactly how that translates to in VB .Net I don't know, although it is
possible to work with ADO Recordset if you use the OleDb .Net data
provider. Then again, who wants to use ADO recordsets if you are in .Net?

I should add that my notion of how we use Crystal is somewhat foggy. I
try to stay away from Crystal as much as I can.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp