question

AypnCNN-4494 avatar image
0 Votes"
AypnCNN-4494 asked JeffreyWilliams-3310 answered

How to concatenate column values in SQL

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:


102244-image.png


sql-server-generalsql-server-transact-sql
image.png (2.1 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AypnCNN-4494,
What is your SQL Server version?

 SELECT @@VERSION;
0 Votes 0 ·
AypnCNN-4494 avatar image AypnCNN-4494 YitzhakKhabinsky-0887 ·

Microsoft SQL Server 2016 (SP1-CU15-GDR) (KB4505221) - 13.0.4604.0 (X64) Jun 15 2019 07:56:34 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered AypnCNN-4494 commented

Hi @AypnCNN-4494,

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 |
 +-------+-------------------+
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,
it seems a very big Select statement, can you make it simply pls.

with regards,

0 Votes 0 ·
JeffreyWilliams-3310 avatar image
1 Vote"
JeffreyWilliams-3310 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft commented

Hi @AypnCNN-4494,

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:
102273-image.png

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.



image.png (2.3 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Noted, It helped me.

thanks,

0 Votes 0 ·

You're welcome, the forum supports accepting multiple replies as answers, as long as you think it is useful to you.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered EchoLiu-msft edited

Try this query:

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

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, I'm getting this err 'concat_ws' is not a recognized built-in function name. it required to write any function?

0 Votes 0 ·

Hi,
Is it possible to deploy concat_ws function in my Microsoft SQL Server 2016 ? or pls provide an alternate way simply

Thanks in advance.

0 Votes 0 ·

No,you can't.Concat_ws applies to SQL Server 2017 (14.x) and later.
For details, please refer to:
CONCAT_WS (Transact-SQL)


0 Votes 0 ·