How to concatenate two different values from two different columns with comma " , " using TSQL?

Question

I would like to know how to concatenate two different values from two different columns from a SQL table using TSQL ?

As you can see, I would like to concatenate those two different columns X e Y, resulting in the follow column table:

Which query should be used here ?

The question is: why do you want to do that in the first place? Formatting, in general, should be left to the client language. — Branko Dimitrijevic, Jan 24 '22 at 14:09
provide dataset rather than attaching a screenshot. it is time consuming to replicate the same dataset. — Moulitharan M, Jan 24 '22 at 14:40
Do any of the columns have a data type other than `pixel`? Is the "NULL" a picture, a string or a `null`? — HABO, Jan 24 '22 at 14:40

score 1 · Answer 1 · answered Jan 24 '22 at 14:09

if the datatype is numeric types(int,bigint, tinyint,smallint,etc) then you need to cast it to string before you concatenate. if the datatype is string(varchar,char,nvarchar,nchar) then you can directly use concat function

select concat(cast(column_1 as varchar) ,cast(column_2 as varchar))

select concat(column_1,column_2)

another workaround, if the columns are string datatype, then

select column_1+column_2

sample

with cte as (select 1 as id, 'name' as Field1, 'job' as Field2, '1test1' as Field1value , '2test1' as Field2value 
union select 2 as id, 'name' as Field1, 'job' as Field2, '1test1' as Field1value , '2test2' as Field2value 
union select 2 as id, 'age' as Field1, 'town' as Field2, '13' as Field1value , 'town1' as Field2value )
select 'select percentage from table2 where '+Field1+' ='+ ''''+Field1value+ ''''+' and '+Field2+' = '+  ''''+Field2value+ '''' from cte

result

Note: null value in any one column will have result as null

Ullas · Answer 2 · 2022-01-24T14:16:36.270

You can simply use + to concatenate charcaters.

Query

select *, x + ',' + y as z
from your_table_name;

If there is a null value in any of the columns, then the concatenation results to a null value.

To handle the null value in any of the column

Query

select *,
case 
  when x is null and y is not null then y
  when y is null and x is not null then x
  when x is null and y is null then null
  else x + ',' + y end as z
from your_table_name;

user8193706 · Accepted Answer · 2022-01-24T15:45:24.933

0

You can use concat as

SELECT 
    Source, QtyPrevious, QtyToday, ProductPrevious, ProductToday, 
    AvaDatePr, AvaDateToday, Clusters, CONCAT(X, '', Y) as Z 
from your_table_name;

edited Jan 24 '22 at 15:45

answered Jan 24 '22 at 14:11

user8193706

2,387
2
8
12

How to concatenate two different values from two different columns with comma " , " using TSQL?

3 Answers3