Flattening Data: Need to eliminate rows, but keep join.

If you want to treat those 2 sites as if they were 35, then you an create a CTE first to eliminate and merge any fields, then use that CTE in place of the original table.

;with cteFacility AS (SELECT DISTINCT f.FacilityID, CASE WHEN F.SiteID IN (135,235) THEN 35 ELSE F.SiteID END As SiteID FROM Facility F)
SELECT s1.SiteID,
     STUFF((SELECT ';'+f.Number
      FROM cteFacility f, [Site] s2
       WHERE s2.SiteID = f.SiteID
          and s2.SiteID = s1.SiteID
         ORDER by f.Number
          FOR XML PATH('')),1,1,'') AS [Number]
     FROM [Site] s1
   GROUP BY SiteID

Two things are not clear
1. what do you mean by dub 135 and 235 as 35. Is it that only the last two digits count?
    -> right(f.Number,2)
2. why 'flattening' when afterwards you want it stripped again
  or is a result like this possible (taking 1. into account)

SiteID      FacilityID
35      35;42

Techies--
Thanks so much for your responses. It is a bit of grappler...as this query on the surface seems so simple to develop... until one tries to accomplish what appears to be the illogical!;-) Flattening the data as I did in the code snippet is the truth of what is stored in the tables-- rolled up for reporting purposes.  

@jogos-- the code snippet I included is just to illustrate what does work, but not necessarily what will work in this new case--so no worries about my commitment to xmp path, stuff, etc.!:-)  You are very close... in that only one value will get returned with your statement:

SELECT s1.SiteID,  min(f.Number) as [Number]
     FROM [Site] s1
     inner join Facility f on s1.SiteID = f.SiteID
   GROUP BY s1.SiteID

...but the value that is returned is '135' -- which is one of the multiple, non-core facilities for a given site. I can
see why min(f.number) would be as good a value to capture as any, because I didn't make it clear that
in each case where there is a site with multiple facilities, one site will retain the same value as the SiteID.

To think this through on another level...

Here's what the joined data would actually look like--simplest case.

Site.SiteID      Facility.FacilityID       Facility.SiteID          

35                   35                             35                          
35                   135                           35
35                   235                           35


So, as the join pics up the multiple facilities for the site, I am trying to retain the value that matches the SiteID while at the same time, rename the next found FacilityID value whatever it  (it won't really be 135 or 235 [so a case won't work]) to the matched value, then the next... and so on.

I suppose as I type this, its basically a foreach row within a cursor type processing (how late '80s is that?)--but surely there's a better way!:-)

...well I take that back by systematically stating case tests won't work... perhaps i've been too rash!;-)

OK, I did try:

SELECT s1.SiteID,
       f.Number as [Number]
   FROM [Site] s1
    inner join Facility f on s1.SiteID = f.SiteID
  WHERE s1.SiteID = f.[Number]
       ORDER BY s1.SiteID


This solves my troubles on multiples and created a whole host of new troubles where I have sites without assigned facilities... but its closer!

Thanks for all of your help.

Although this isn't exactly the solution--it is close ...especially for anyone coming behind me seeking a way to filter out multiple returns by the minimum value found.

Showing how interpretations of a question can go other directions

Just returning one value

isnull(min(Case when f.number =s1.SiteID then s1.SiteId else null end) , min(f.Number))

Select allOpen in new window

What do we do?
 - case when -> when it matches siteId-> keep siteID else NULL
 - isnull  -> when not siteId is found then take another

Multiple rows when not siteId found

-- WHERE matches
 SELECT s1.SiteID, 
       f.Number as [Number]
   FROM [Site] s1
    inner join Facility f on s1.SiteID = f.SiteID
   and s1.SiteID = f.[Number]
  UNION
  -- where no match exists
  SELECT s1.SiteID, 
       f.Number as [Number]
   FROM [Site] s1
   and not exists (select 1from Facility f 
                          where  s1.SiteID = f.SiteID
                        and s1.SiteID = f.[Number] )

Select allOpen in new window

Your rollup but with the siteId-predicate

 SELECT s1.SiteID, 
       isnull(f.Number as [Number],       put your rollup here    )                      
   FROM [Site] s1
    left join Facility f on s1.SiteID = f.SiteID
   and s1.SiteID = f.[Number]

Select allOpen in new window

Left join on facility with matching -> when not found then the isnull activates your rollup

A little warning about the rollup (or other select/function) in the SELECT. It is executed for every record so it's not so good for performance. Remember that when you write on large numbers of records or want to do that for multiple columns.