CODEWITHSUNDEEP
sqlsql-server
Rick
Rick

Reputation: 1559

Stuff query in SQL

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

enter image description here

I want to concatenate values with '&' separated.

Desired output:

enter image description here

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

Upvotes: 3

Views: 3229

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You want concat_ws() functionality. You can do this in SQL Server as:

select t.cid,
       stuff( (coalesce('&' + t.val1, '') +
               coalesce('&' + t.val2, '') +
               coalesce('&' + t.val3, '') +
               coalesce('&' + t.val4, '')
              ), 1, 1, ''
            ) as vals               
from #temp t;

This is the most convenient equivalent to concat_ws() that I've found in SQL Server.

If the values are integers, just cast them:

select t.cid,
       stuff( (coalesce('&' + cast(t.val1 as varchar(255)), '') +
               coalesce('&' + cast(t.val2 as varchar(255)), '') +
               coalesce('&' + cast(t.val3 as varchar(255)), '') +
               coalesce('&' + cast(t.val4 as varchar(255)), '')
              ), 1, 1, ''
            ) as vals               
from #temp t;

Here is a db<>fiddle.

Upvotes: 2

Mathew Paxinos
Mathew Paxinos

Reputation: 964

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

Upvotes: 3

Alexander Volok
Alexander Volok

Reputation: 5940

I have a table that stores multiple values for same ID.

If CID values are not unique:

SELECT DISTINCT
       t2.cid,
       REPLACE(STUFF(
       (
           SELECT '|' + CAST(val AS VARCHAR(50))
           FROM
           (
               SELECT cid,   val1 AS val  FROM #temp     
               UNION ALL
               SELECT cid,  val2 AS val   FROM #temp
               UNION ALL
               SELECT cid,  val3 AS val   FROM #temp
               UNION ALL
               SELECT cid,  val4 AS val   FROM #temp
           ) t1
           WHERE t1.cid = t2.cid
                 AND t1.val IS NOT NULL
           FOR XML PATH('')
       ),    1, 1, ''), '|', '&') AS 'output'
FROM #temp t2;

Results to:

cid     output
1001    10&11&15&19
1002    15&16&18
1003    14&18&15

Otherwise, CONCAT or CONCAT_WS as other members correctly suggested is more elegant solution

Upvotes: 1

Joakim Danielson
Joakim Danielson

Reputation: 51851

Use CONCAT_WS for this 

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

Upvotes: 2

Related Questions