Query Help

hortoristic · June 9th, 2004, 10:59 AM

Having a brain fart and I know this is simple but Iâve already wasted a couple hours â so I was wondering if someone could help.

Example Source Table:
Id: Company: StatesCovered:
1 ABC WA
2 Car Toys IL
3 Car Toys WA
4 Car Toys FL
5 Car Toys NY
6 Frys CA
7 Frys WA
8 Frys KY
9 Bobs NY
10 Bobs KY

Result Set Desired:
Company: StatesCovered:
ABC WA
Car Toys IL, WA, FL, NY
Frys CA, WA, KY
Bobs NY, KY

happygv · June 9th, 2004, 09:45 PM

Hi Hortoristic,

This has been discussed many times in the other forum(SQL LANGUAGE) under different topics. You may take a look at it.

Aggregate Concatenation

How to Concatenate Data

But IMO, the best thing possible to get there, is to be done from the frontend. SQL is not the right thing to do such formatting.

Logic for doing this in the frontend could be,[list=1][*]Have 2 frontend variables Company and StatesCovered[*]

Code:

Select Company, StatesCovered from YOURTABLE Order by Company

[*]Get that in a recordset and loop through till END of it[*]When the Company value is the same for every next record, keep storing the StatesCovered Value in a String seperated by Commas[*]Everytime Company value changes display the Company Name and StatesCovered String on the page and reset the StatesCovered String to EMPTY
</ol id="1">

This way you can achieve that. But I am not sure, if you intend to do this in frontend.
Hope that helps.
Cheers!

_________________________
-Vijay G

Strive for Perfection