If the columns are nullable then you can concatenate.
CREATE TABLE House
(House_No_Start VARCHAR(10) NULL,
House_No_End VARCHAR(10) NULL,
House_Name VARCHAR(50) NULL);
GO
INSERT INTO House VALUES
('1', '3', NULL),
('2', NULL, NULL),
(NULL, NULL, 'The Manor');
GO
SELECT
ISNULL(House_No_Start, '') + ISNULL(' - ' + House_No_End, '') + ISNULL(House_Name, '') AS House
FROM
House;
GO
DROP TABLE House;
