How to merge two columns in sql server

Question

I want to merge two columns from deferent tables and when run my query shows this error:

Cannot resolve collation conflict for column 1 in SELECT statement

Here is my query

select (problems.name + department.dep_name) as m, usere.Use_name
    , emp_problems.op_date  
from usere, department, problems, emp_problems 
where usere.id = emp_problems.users_id and emp_problems.prob_id = problems.id   
and emp_problems.dup_dep_id = department.id
and emp_problems.emp_id = 37 and emp_problems.months = 11 and emp_problems.years =2020    

Answer 1

You need to ALTER one of the columns to change the collation, or use COLLATE clause as

CREATE TABLE dbo.MyTable
(
  MyCol1 VARCHAR(20) COLLATE Latin1_General_100_CI_AI_SC,
  MyCol2 VARCHAR(20) COLLATE Latin1_General_100_CI_AI
);

Using ALTER:

ALTER TABLE dbo.MyTable 
ALTER COLUMN MyCol2 VARCHAR(50) COLLATE Latin1_General_100_CI_AI_SC;

Using COLLATE clause:

INSERT INTO dbo.MyTable VALUES
('Foo', 'Bar');
/* This will throw the error */
SELECT CONCAT(MyCol1, MyCol2) FROM dbo.MyTable;

/* This won't */
SELECT CONCAT(MyCol1, MyCol2 COLLATE Latin1_General_100_CI_AI_SC) FROM dbo.MyTable;

Take a look at Set or Change the Column Collation

Here is a db<>fiddle