User Profile

K3nSchr0eder · Feb 5 '13, 01:08 AM

Thank you both sooo much for your assistance. I have now got exactly what I wanted.
Here is the final query:

Code:

SELECT     HomeCareAgencies.*,
           ((SELECT DISTINCT',' + Services 
              FROM ILCounties 
              WHERE HomeCareAgencies.Mnemonic = ILCounties.Mnemonic
              FOR XML PATH('')))AS ILService,
              ((SELECT DISTINCT ',' + County
                FROM ILCounties

...

K3nSchr0eder · Feb 4 '13, 08:22 PM

@Rabbit YOU ARE A GENIUS!!
So here is the code I have got so far:

Code:

SELECT HomeCareAgencies.*,MoCounties.ServicesOffered,MoCounties.Mnemonic,
                ((SELECT DISTINCT County + ',' 
                      FROM MoCounties AS MC
                      WHERE MC.Mnemonic = HomeCareAgencies.Mnemonic
                      FOR XML PATH('')))AS MoCounty
FROM         [HomeCareAgencies],MoCounties

...

K3nSchr0eder · Feb 4 '13, 07:03 PM

@ Rabbit,

I now have this statement:

Code:

SELECT     HomeCareAgencies.*,ILCounties.County,ILCounties.Mnemonic,ILCounties.Services,MoCounties.County,
          
                     ((SELECT',' + Services
                      FROM ILCounties AS ILC
                      FOR XML PATH('')))AS ILServicesAndCounties
                      
          
FROM         HomeCareAgencies LEFT JOIN

...

K3nSchr0eder · Feb 4 '13, 04:25 PM

@NeoPa and @Rabbit- Thank you both for your responses. I cant't combine the Counties tables because they are used in a different program independently. So that is one of the hurdles I have. I guess the main issue I am trying to resolve is, that if one of the Agencies provides 5 different services it list that agency 5 times. I need it to list the agency 1 time with all 5 services listed for that 1 agency. I think that is what @Rabbit is trying to...

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Trying to do a COUNT/AS RANK and it will not work.