Stuff query in SQL

Question

I have a table that stores multiple values for same ID. The table looks like below:

I want to concatenate values with '&' separated.

Desired output:

My query is not producing desired output. Below is my query:

create table #temp (cid int, val1 int, val2 int, val3 int, val4 int)
  insert #temp values
    (1001,10,11,15,19),
    (1002,15,Null,16,18),
    (1003,14,18,15,NULL)

SELECT distinct t2.cid,
        STUFF(( SELECT '&' + REPLACE(t1.val1,'.','') +
                             REPLACE(t1.val2,'.','') +
                             REPLACE(t1.val3,'.','') + 
                             REPLACE(t1.val4,'.','')
                FROM #temp t1
                WHERE t1.cid = t2.cid
                FOR XML PATH ('')
            ), 1, 1,'') as 'output'
        FROM #temp t2

Note: I am using SQL Server 2014

Answer 1

For SQL Server 2017 you can use CONCAT_WS (complements to @Joakim Danielson):

SELECT cid, CONCAT_WS('&', val1, val2, val3, val4)
FROM #temp

For SQL Server 2012 you can use CONCAT:

SELECT cid, CONCAT(val1, '&', val2, '&', val3, '&', val4)
FROM #temp

For all Versions of SQL Server you can use the '+' to concatenate.

SELECT cid, val1 + '&' + val2 + '&' + val3 + '&' + val4
FROM #temp

Given that your example includes NULL and appears to be storing integers, I would recommend the following:

SELECT  cid
       ,STUFF(COALESCE( '&' + CONVERT(VARCHAR, val1), '')
             + COALESCE( '&' + CONVERT(VARCHAR, val2), '')
             + COALESCE( '&' + CONVERT(VARCHAR, val3), '')
             + COALESCE('&' + CONVERT(VARCHAR, val4), ''), 1, 1, '')
FROM #temp

*Accepted edit to include STUFF to remove the trailing '&'

The COALESCE() will have the effect of not including NULL values in your listing, while the CONVERT to varchar will handle the Integers