SQL Server : concat last column as one field for the same columns

Question

I have a table:

Table1

fname   lname      job         education
----------------------------------------
john    doe        dentist     harvard
john    doe        dentist     mit
eric    doyle      lawyer      mit
anna    johnson    nurse       yale

Trying to select all columns

select fname, lname, job, education 
from table1

But this returns output like

john doe dentist harvard
john doe dentist mit
eric doyle lawer mit
anna johnson nurse yale

How can I make it like this:

john doe dentist harvard, mit
eric doyle lawer mit
anna johnson nurse yale

In other words the same person "john doe", education is concatenated into one column with one row.

Was thinking to write a Python script that:

1. Read column 1,column 2 and other column except the last one, stores the values
2. Check if 2nd row column1 and column2 is different or the same
3. If the same, read column 4 from 1 row and 2 row and concat it into the string
4. Update the 4 column to have concatenated value
5. if different, go further

Need some tips! Thanks in advance.

Answer 1

Try this:

CREATE TABLE #Table1(fname VARCHAR(20), lname VARCHAR(20),job VARCHAR(20),education VARCHAR(20))

INSERT INTO #Table1

SELECT 'john', 'doe'     ,'dentist', 'harvard' UNION ALL
SELECT 'john', 'doe'     ,'dentist', 'mit'    UNION ALL
SELECT 'eric', 'doyle'   ,'lawer'  , 'mit'    UNION ALL
SELECT 'anna', 'johnson' ,'nurse'  , 'yale'


SELECT T1.fname,T1.lname,T1.job,
STUFF(( SELECT  ', ' + cast(T2.education as varchar(5))
                    FROM    #Table1 T2 
                    WHERE   T1.fname = T2.fname AND T1.lname = T2.lname
                    FOR XML PATH('')
                ), 1, 2, '') as education

FROM #Table1 T1
GROUP BY T1.fname,T1.lname,T1.job

Answer 2

You need to use 1,1 value as education

select fname, lname, job, stuff ((
  select ',' + education from #Table1 where fname = t.fname and lname = t.lname and job = t.job 
for xml path('')
),1,1,'') as education
from #Table1 t
group by fname, lname, job