The first query is just a reiteration of what djj just posted. The idea is that you use the row_number window function to partition, or group, the data by name, and the order it by server speed. You end up with an incrementing number over each group (or partition) of names ordered from the lowest speed to the highest, Then you select out the one with a value of 1, knowing it will correspond to the rows with the lowest speed per server.

select *

from (select

RID = row_number() over(partition by Name order by Speed),

Name,

[Server],

Speed

from #CustSiteInfo) a

where a.RID = 1

The second query uses an xml function to concatenate row values into a single column. Starting from the inner-most section, you concatenate all the servers with a pipe character, and using a correlated subquery (i.e. joining the contents of that xml query to the outer table aliased as "a") tie each row to the correct name.

In the next higher level where I've got name and that big xml subquery being returned, I've grouped it by Name. If you don't you'll have duplicate rows (as many as you have servers for a given site name). Finally, the reason I put that in another subquery was simply to chop off the trailing pipe. There are other ways to do this, but this is my preferred way. Let me know if you have any questions on what's going on here.

select

Name,

ServerList = left(ServerList, len(ServerList) - 1)

from (select

a.Name,

ServerList = (select b.[Server] + '|'

from #CustSiteInfo b

where a.Name = b.Name

for xml path (''))

from #CustSiteInfo a

group by a.Name) z