sql server mseq: 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

I created teh query and it works great - below is the code - I Just need to
change the dum column- it's sort of a Reverse Join scenario:

DECLARE @StatesCovered varchar(255), @dum varchar(255)
SELECT
@dum = 'Mycompany', @StatesCovered = COALESCE(@StatesCovered + ',', '') +
cast(x.enfact_osha_num AS varchar)
FROM
(SELECT z.enfact_osha_num FROM enfact z JOIN enreac k ON z.enfact_id =
k.enreac_related_enfact_id
WHERE k.enreac_del_date IS NULL AND k.enreac_enfact_id = 16206) AS x
SELECT @dum AS dum,
@tmp AS tmp

Returns:
dum tmp
MyCompany KY, NY, WA, FL



[quoted text, click to view]

There is no single SQL query which can accomodate such reporting
requirements so it is generally recommended that you use a client side
programming language or a report writer to develop such formatted results.

Some options in SQL for such problems are discussed here:
http://groups.google.com/groups?selm=%23u19EkNHEHA.700%40TK2MSFTNGP09.phx.gbl

--
Anith