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

user_14537170 0
Viewed 186

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.

sql-server-2016 sql-server sql
2 Answers

One convenient way is:

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

Here is another method by using XML and XQuery.

The number of columns is not hard-coded, it could be dynamic.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, col1 CHAR(1), col2 CHAR(1), col3 CHAR(1), col4 CHAR(1));
INSERT INTO @tbl (col1, col2, col3, col4) VALUES
( 'A',  'B',  NULL, 'D'),
(NULL, 'E' , 'F'  , 'G'),
(NULL, NULL, NULL , NULL);
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '|';

SELECT id, REPLACE((
    SELECT * 
    FROM @tbl AS c
    WHERE c.id = p.id
    FOR XML PATH('r'), TYPE, ROOT('root')
).query('data(/root/r/*[local-name() ne "id"])').value('.', 'VARCHAR(100)') , SPACE(1), @separator) AS concatColumns
FROM @tbl AS p;

Output

+----+---------------+
| id | concatColumns |
+----+---------------+
|  1 | A|B|D         |
|  2 | E|F|G         |
|  3 |               |
+----+---------------+
user_19323110
Related