How to concatenate column values in SQL

Question

Hi,

ref my table, I want CONCAT column values, zeros should not include.

My Table:

Drop Table #Temp   
Create Table #Temp(RowId Int,DueDate1 int, DueDate2 int, DueDate3 int, DueDate4 int, DueDate5 int)  
Insert into #Temp (RowId, DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(1,1,0,3,0,5)  
Insert into #Temp (RowId,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(2,1,0,0,4,0)  
Insert into #Temp (RowId,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(3,1,2,3,4,5)  
  
Select * From #Temp  

Expected result:

Answer 1

Hi @Aypn CNN ,

Here is an alternative method that would work for SQL Server version before 2017.
Additional functionality is that number of columns could vary. So the table could have DueDate6, ..., DueDateN columns.

SQL

-- DDL and sample data population, start  
DECLARE @tbl Table (RowID Int IDENTITY PRIMARY KEY,DueDate1 int, DueDate2 int, DueDate3 int, DueDate4 int, DueDate5 int);  
Insert INTO @tbl (DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) VALUES  
(1,0,3,0,5),  
(1,0,0,4,0),  
(1,2,3,4,5);  
-- DDL and sample data population, end  
  
SELECT p.RowID   
 , REPLACE(((  
 SELECT *   
 FROM @tbl AS c  
 WHERE c.RowID = p.RowID  
 FOR XML PATH(''), TYPE, ROOT('root')   
 ).query('  
 for $x in /root/*[local-name()!="RowID"]/text()[. ne "0"]  
 return data($x)  
 ').value('.', 'VARCHAR(50)'))  
 , SPACE(1), ' | ') AS Result  
FROM @tbl AS p;  

Output

+-------+-------------------+  
| RowID |      Result       |  
+-------+-------------------+  
|     1 | 1 | 3 | 5         |  
|     2 | 1 | 4             |  
|     3 | 1 | 2 | 3 | 4 | 5 |  
+-------+-------------------+  

Answer 2

Here is a version using CONCAT that works on 2016:

 Drop Table If Exists #Temp 
 Create Table #Temp(RowId Int,DueDate1 int, DueDate2 int, DueDate3 int, DueDate4 int, DueDate5 int)
 Insert into #Temp (RowId, DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(1,1,0,3,0,5)
 Insert into #Temp (RowId,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(2,1,0,0,4,0)
 Insert into #Temp (RowId,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(3,1,2,3,4,5)
 Insert into #Temp (RowId,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(3,0,2,3,4,5)
 Insert into #Temp (RowId,DueDate1, DueDate2, DueDate3, DueDate4, DueDate5) Values(3,0,0,3,4,5)

 Select *
      , DueDates = stuff(replace(concat('|', DueDate1, '|', DueDate2, '|', DueDate3, '|', DueDate4, '|', DueDate5), '|0', ''), 1, 1, '')
   From #Temp  

I added a couple of rows with 0 for DueDate1 and DueDate2 - to verify the results work.

Answer 3

Try this query:

select RowId,
    concat_ws(' | ', nullif(DueDate1, 0), nullif(DueDate2, 0), nullif(DueDate3, 0), nullif(DueDate4, 0), nullif(DueDate5, 0))
from #Temp 

Answer 4

Hi @Aypn CNN ,

Welcome to the microsoft TSQL Q&A forum!

Your question is related to tsql, so my colleague helped you add the tsql tag. So that you can get more professional help from people on the tsql forum.

Please also check:

SELECT RowId,CONCAT(IIF(DueDate1=0,NULL,CAST(DueDate1 AS VARCHAR)+'|'),  
IIF(DueDate2=0,NULL,CAST(DueDate2 AS VARCHAR)+'|'),  
IIF(DueDate3=0,NULL,CAST(DueDate3 AS VARCHAR)+'|'),  
IIF(DueDate4=0,NULL,CAST(DueDate4 AS VARCHAR)+'|'),   
IIF(DueDate5=0,NULL,CAST(DueDate5 AS VARCHAR)+'|'))DueDates  
FROM #Temp  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.