Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
boybles
Starting Member
23 Posts |
 Posted - 2009-06-25 : 23:09:44
|
I've been racking my brain for hours trying to figure out the SQL on how to join all these columns together and create a comma delimited list of areas associated with each store. The following lays out the tables/columns and desired result. Please, please help! Thanks  TonyStore----------StoreID(int)StoreName(varchar)StoreType(int)StoreType----------TypeID(int)TypeName(varchar)Area-----------AreaID(int)AreaName(varchar)AreaStoreMap--------------MapID(int)StoreID(int)AreaID(int)Store---------------------------StoreID|StoreName|StoreType---------------------------1|StoreA|12|StoreB|13|StoreC|24|StoreD|1StoreType-----------------TypeID|TypeName------------------1|Pharmacy2|MarketArea-------------------AreaID|AreaName-------------------1|Nevada2|Washington3|Oregon4|KansasAreaStoreMap-------------------MapID|StoreID|AreaID---------------------1|2|12|2|33|2|24|1|45|1|36|4|2Desired Result (where Areas is a delimited list of areaIDs)--------------------------------StoreID|StoreName|TypeName|Areas---------------------------------1|StoreA|Pharmacy|4,32|StoreB|Pharmacy|1,3,23|StoreC|Market|4|StoreD|Pharmacy|2 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-25 : 23:10:45
|
if you are using SQL 2005 you can use this method concatenate records without UDF
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
boybles
Starting Member
23 Posts |
Posted - 2009-06-25 : 23:18:44
|
| Thanks. That does shed some light, but the joins are what confuse me combined with the list. Especially when you see that one of the stores (StoreC) doesn't have any areas mapped but still needs to be a part of the result set. What would the SQL look like? ThanksTony |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-25 : 23:34:29
|
[code]select s.StoreID, s.StoreName, t.TypeName, Areas = STUFF((SELECT ',' + convert(varchar(10), x.AreaID) FROM AreaStoreMap x WHERE x.StoreID = s.StoreID FOR XML PATH('')), 1, 1, '') from Store s inner join StoreType t on s.StoreType = t.TypeID[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
boybles
Starting Member
23 Posts |
Posted - 2009-06-26 : 01:08:42
|
| Khatan,That works perfectly. Thank you very, very much for your guidance. BTW, was there a way to solve this kind of situation (without UDF) before SQL Server 2005? Just curious.Thanks again for your invaluable help.Tony |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
|
|
|
|
Multiple Join With Comma Delimited List Column