Most systems will have a user-defined function (UDF) available. Some will not. i want to use the UDF if it's there:

SELECT 
    Users.*,
    dbo.UserGroupMembershipNames(Users.UserID) AS MemberOfGroupNames
FROM Users

Otherwise fallback to the acceptable alternative

SELECT
   Users.*,
   (SELECT TOP 1 thing FROM Something 
    WHERE Something.ID = Users.UserID) AS MemberGroupNames
FROM Users

How do?

My first attempt, using the obvious solution, of course failed:

SELECT 
    Users.*,
    CASE
    WHEN (OBJECT_ID('dbo.UserGroupMembershipNames') IS NOT NULL) THEN
           dbo.UserGroupMembershipNames(Users.UserID)
    ELSE (SELECT TOP 1 thing FROM Something 
          WHERE Something.ID = Users.UserID)
    END AS MemberOfGroupNames
FROM Users

for reasons beyond me