How to Remove the duplicates in SQL Table and concat other part

Question

Let's Assume I have DataTable /SQL Table(MicroSoft SQL) that represents employee information for example

which contains firstname,lastname,age,company,yearsofexperience,Degree

I want to combine information based on firstname,lastname,age

company,yearsofexperience,Degree must be concat into corresponding cell

firstname   lastname   age   company   yearsofexperience      Degree

john         muller     21    IBM           4years            MBA   
jan          tonny      22,   MSoft         1years            MS
martin       tata       21    apple         2years            PHD
john         Muller     21    sony          3years            MBA
james        muller     21    IBM           4years            PHD   
jan          tonny      22    Telsa         1years            BS     
martin       tata       21    sun           2years            MBA
james        Muller     21    TCS           3years            BS

Note:MS SQL solution not Mysql

Please find me way to remove the duplicate rows and make other data concat in particular column

For example in from above example I want combine the information present in the similar other 3 entries

firstname   lastname   age   company            yearsofexperience      Degree

john      muller        21   IBM,sony,              4years,3years,        MBA,MBA   
jan       tonny         22,  MSoft,Telsa            1years,1years         MS,BS
martin    tata          21   apple,sun              2years,2years         PHD,MBA
james     muller        21   IBM,TCS               4years,3years          PHD,BS

Right I am looking for what is best ways to implement this

Its good approach if I Split the Tables in to 2 different tables? may be based on Primary key match. we can con cat other entries ?

Please help me out thanks(+1) in advance

Answer 1

If you are using SQL Server then try this:

SELECT T1.firstname
,t1.lastname
,t1.age
,Company = SubString (( SELECT ', ' + T2.company 
FROM table_name as T2 
WHERE T1.firstname = T2.firstname
and t1.lastname = t2.lastname
and t1.age = t2.age 
FOR XML PATH ( '' ) ), 3, 1000) 

,yearsofexperience = SubString (( SELECT ', ' + T2.yearsofexperience 
FROM table_name as T2 
WHERE T1.firstname = T2.firstname
and t1.lastname = t2.lastname
and t1.age = t2.age 
FOR XML PATH ( '' ) ), 3, 1000) 

,yearsofexperience = SubString (( SELECT ', ' + T2.Degree 
FROM table_name as T2 
WHERE T1.firstname = T2.firstname
and t1.lastname = t2.lastname
and t1.age = t2.age 
FOR XML PATH ( '' ) ), 3, 1000) 

FROM table_name as T1 
GROUP BY T1.firstname, t1.lastname ,t1.age

Reference: Create A Comma Delimited List From a Column in SQL Server

Answer 2

You can try group by and group_concat of SQL

SELECT *,group_concat(company) as company,
group_concat(yearsofexperience) as yearsofexperience,
group_concat(Degree) as Degree  
from table group by firstname
group by lastname
group by age

for more follow the link Merge data in two row into one