query that merges data from multiple rows

[millerk]

Say I have these 2 tables

ProductRules: ProductNum, RuleCode
this table says that a particular rule applies to a particular product.

RuleCountryLink: Rule Code, Country
this table says that a particular rule is applicable to a particular country.

So right now, I have a query that is something like:
select ProductNum, RuleCode, Country
from ProductRules pr join RuleCountryLink rcl
on pr.RuleCode = rcl.RuleCode

and it returns something like this:

ProductNum---RuleCode---Country
1--------------A--------Mexico
1--------------A--------Canada
1--------------A--------Sweden
1--------------L--------Germany
1--------------L--------Mexico
1--------------L--------France
2--------------A--------United States
2--------------A--------Italy

What I would really like is to have 1 record for each unique combination of Product and Rule and a list of all the countries this applies to. Something like:

ProductNum---RuleCode---Country
1--------------A--------Mexico, Canada, Sweden
1--------------L--------Germany, Mexico, France
2--------------A--------United States, Italy

I'm doing this in ASP.Net, so if I need to, I'll just get the results like the first listing, make sure they are ordered correctly, and loop through them to build the display. But I wanted to check first and see if anyone had a good way of doing this in SQL. I can't think of a way to do it without using a cursor to loop through and populate a temp table or something.

Any better ideas?

 

[AlexCuse]

I think your better off doing this in your front end, unless you will be returning a truly massive result set if you do it that way. The .net framework will give you much more powerful string manipulation functions to get your comma separated list.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Read this to see if it helps.

thread183-1159740


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom