How to concat strings with space delimiter where each string is nullable?

Question

I have a table with multiple nullable string fields.

For reporting I would like to combine them into a single string that is delimited with a space for every field. The right side must be trimmed.

Test data:

DECLARE
  @test 
TABLE
(
    f1 NVARCHAR(100)
  , f2 NVARCHAR(100)
  , f3 NVARCHAR(100)
)

INSERT INTO @test 
-- NULL values must be skipped
      SELECT NULL       , NULL , NULL
UNION SELECT NULL       , NULL , 'foo'
UNION SELECT NULL       , 'foo', NULL
UNION SELECT 'foo'      , NULL , NULL
UNION SELECT NULL       , 'foo', 'bar'
UNION SELECT 'foo'      , 'bar', NULL
UNION SELECT 'foo'      , NULL , 'bar'
UNION SELECT 'foo'      , 'bar', 'baz'
-- Empty string values must be skipped
UNION SELECT 'foo'      , ''   , 'bar'
UNION SELECT ''         , ''   , 'baz' 
-- Spaces in values must be kept
UNION SELECT 'foo   bar', ''   , 'baz'
-- The table should not contain values with surrounding space
-- So this case is not important
UNION SELECT ' foo '    , ' '  , 'baz '

The expected results:

''
'foo'
'foo'
'foo'
'foo bar'
'foo bar'
'foo bar'
'foo bar baz'
'foo bar'
'baz'
'foo   bar baz'
'foo  baz'
' foo   baz' -- This is optional

My current code:

SELECT
  RTRIM(
    CASE WHEN ISNULL(f1, '') = '' THEN '' ELSE f1 + ' ' END
  + CASE WHEN ISNULL(f2, '') = '' THEN '' ELSE f2 + ' ' END
  + CASE WHEN ISNULL(f3, '') = '' THEN '' ELSE f3 + ' ' END
  )
FROM
  @test

This works, but I was wondering if there is a less verbose way to achieve this?

Answer 1

In SQL Server 2012, there is a CONCAT function. You can use the following as well:

SELECT
  RTRIM(CONCAT(  
      NULLIF(f1, '') + ' '
    , NULLIF(f2, '') + ' '
    , NULLIF(f3, '') + ' '
  ))
FROM
  @test