How to concatenate two different values from two different columns with comma " , " using-CodePudding

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 ?

CodePudding user response：

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

CodePudding user response：

You can use also concat as

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

CodePudding user response：

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;