how to Concat data value in sql server

Question

I have table A in database but I want to know how to change or concat it to table B with a SQL command in SQL Server like this:

Answer 1

If you are on SQL Server 2017+ you can use STRING_AGG to achieve this:

SELECT  [Personal Code],
        STRING_AGG(Unit, ' / ') AS Unit
  FROM  [Table A]
  GROUP BY [Personal Code]

Output:

Personal Code   Unit
981203030       Albani / Italy / England
981203031       France / Spain / Denmark
981203033       Russia / Peru

Here's the working fiddle for this.

You can then insert this directly into Table B:

INSERT INTO [Table B] ([Personal Code], Unit)
    SELECT  [Personal Code],
            STRING_AGG(Unit, ' / ')
      FROM  [Table A]
      GROUP BY [Personal Code]

Answer 2

if you are using lower version of MSSQL

Select distinct ST2.[Personal Code], LTrim(substring((Select ' / '+ ST1.unit  AS [text()]
                                From taba ST1 Where ST1.[Personal Code]= ST2.[Personal Code]
                                ORDER BY ST1.[Personal Code] For XML PATH ('')), 3, 1000)) Unit
From taba ST2