Concatenate or merge many columns values with a separator between and ignoring nulls - SQL Server 2016 or older

Question

I want to simulate the CONCAT_WS SQL Server 2017+ function with SQL Server 2016 version or older in order to concatenate many columns which values are strings like that:

Input:

| COLUMN1 | COLUMN2 | COLUMN3 | COLUMN4 |
   'A'        'B'       NULL     'D'
   NULL       'E'       'F'      'G'
   NULL       NULL      NULL     NULL

Output:

| MERGE |
 'A|B|D'
 'E|F|G'
  NULL

Notice that the output result is a new column that concatenate all values separated by '|'. The default value should be NULL if there are no values in the columns.

I tried with CONCAT and a CASE statement with many WHEN conditions but is really dirty and I am not allowed to use this solution. Thanks in advance.

question from:https://stackoverflow.com/questions/65924066/concatenate-or-merge-many-columns-values-with-a-separator-between-and-ignoring-n

Answer 1

One convenient way is:

select stuff( coalesce(',' + column1, '') +
              coalesce(',' + column2, '') +
              coalesce(',' + column3, '') +
              coalesce(',' + column4, ''), 1, 1, ''
            )