I frequently use (and prefer) the method that Sean has posted. One caveat, watch out for empty strings rather than nulls.

What is the rule for all fields populated?

Here's a couple of options to handle that situation.

--House Numbers have priority over house name

SELECT COALESCE(

House_No_Start + ISNULL(' - ' + House_No_End, ''),

House_Name + ','

) AS House

--Retain House Name prior to number range

SELECT ISNULL(House_Name,'') + -- House_name or empty

ISNULL(SUBSTRING(', ' + House_Name + House_No_Start,1,2),'') + -- Comma if house_name and house_no_start

ISNULL(House_No_Start, '') + ISNULL(' - ' + House_No_End, '') -- House number range or empty

AS House