Merge Results into one cell

Question

I am trying to pull out a list of staff from our management information system database which includes their firstname, surname, Role and Subjects taught.

The following SQL Query:

Select TblStaff.Firstname, TblStaff.Surname, txtSchoolRolesName
from 
TblStaff 
LEFT JOIN TblStaffManagementSchoolRoles ON TblStaff.TblStaffID = TblStaffManagementSchoolRoles.intStaff
LEFT JOIN TblStaffManagementSchoolRolesObjects ON TblStaffManagementSchoolRoles.intSchoolRole = TblStaffManagementSchoolRolesObjects.TblStaffManagementSchoolRolesObjectsID
WHERE TblStaff.SystemStatus = 1

Returns the following:

But I need it to look like this:

How would be best to do this?

Please edit your question to add [**sample data**](http://plaintexttools.github.io/plain-text-table/) and the expected output based on that data. Provide them as [**Formatted text**](http://stackoverflow.com/help/formatting) and strictly [**no screen shots**](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). **DO NOT** post code or additional information in comments. Please ensure you have a [**minimal, complete and verifiable example**](https://stackoverflow.com/help/mcve). — Srini V, Sep 25 '17 at 12:53
May I just tell you that it is a bad idea to store your values like that? Also add a tag to state which database you are using. — PacoDePaco, Sep 25 '17 at 12:55
Possible duplicate of [Concatenate many rows into a single text string?](https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Stephan Bauer, Sep 25 '17 at 12:57
Please provide some example data and schema. Given your query, I can't make data come out the way yours did. You may have a data problem, too. — Shawn, Sep 25 '17 at 15:05

Kashif Qureshi · Answer 1 · 2017-09-25T13:44:52.780

you can use Stuff() to concatenate values into single cell

Here is an example:

create table #temp (
firstname varchar(255),
surname varchar(255),
[role] varchar(255),
[subject] varchar(255)

)

insert into #temp
values ('Jane', 'Smith', 'Maths Teacher', 'Math'),
 ('Jane', 'Smith', 'Maths Teacher', 'Physics'),
 ('Jane', 'Smith', 'Maths Teacher', 'Tutorial'),
 ('Jane', 'Smith', 'Physics Teacher', 'Math'),
 ('Jane', 'Smith', 'Physics Teacher', 'Physics'),
 ('Jane', 'Smith', 'Physics Teacher', 'Tutorial'),
 ('Kate', 'Smith', 'Maths Teacher', 'Math1'),
 ('Kate', 'Smith', 'Maths Teacher', 'Physics'),
 ('Kate', 'Smith', 'Maths Teacher', 'Tutoria'),
 ('Kate', 'Smith', 'Physics Teacher', 'Math'),
 ('Kate', 'Smith', 'Physics Teacher', 'Physics'),
 ('Kate', 'Smith', 'Physics Teacher', 'Tutorial')

select * from #temp


select distinct firstname,surname, STUFF( (SELECT distinct ',' + [role] 
                             FROM [#temp] t1
                             where t1.firstname = t2.firstname and t1.surname = t2.surname
                             FOR XML PATH('')), 
                            1, 1, ''),

            STUFF( (SELECT distinct ',' + [subject] 
                FROM #temp t1
                 where t1.firstname = t2.firstname and t1.surname = t2.surname                   
                FOR XML PATH('')), 
            1, 1, '') as [subject]
from #temp t2

drop table #temp

Another way this could be achive by using Cross APPLY

create table #temp (
firstname varchar(255),
surname varchar(255),
[role] varchar(255),
[subject] varchar(255)

)

insert into #temp
values ('Jane', 'Smith', 'Maths Teacher', 'Math'),
    ('Jane', 'Smith', 'Maths Teacher', 'Physics'),
    ('Jane', 'Smith', 'Maths Teacher', 'Tutorial'),
    ('Jane', 'Smith', 'Physics Teacher', 'Math'),
    ('Jane', 'Smith', 'Physics Teacher', 'Physics'),
    ('Jane', 'Smith', 'Physics Teacher', 'Tutorial'),
    ('Kate', 'Smith', 'Maths Teacher', 'Math1'),
    ('Kate', 'Smith', 'Maths Teacher', 'Physics'),
    ('Kate', 'Smith', 'Maths Teacher', 'Tutoria'),
    ('Kate', 'Smith', 'Physics Teacher', 'Math'),
    ('Kate', 'Smith', 'Physics Teacher', 'Physics'),
    ('Kate', 'Smith', 'Physics Teacher', 'Tutorial')


select distinct firstname,surname,rol.[role], sub.subject
from #temp t2
CROSS APPLY (SELECT convert(varchar(20), [role]) + ','
                            FROM #temp t1
                            where t1.firstname = t2.firstname and t1.surname = t2.surname                                  
                            GROUP BY firstname,surname,[role]
                                    FOR XML PATH('')) rol([role])
CROSS APPLY (SELECT    convert(varchar(20), [subject]) +',' 
                    FROM #temp t1
                    where t1.firstname = t2.firstname and t1.surname = t2.surname                                  
                    GROUP BY  firstname,surname,[subject]
                            FOR XML PATH('')) sub([subject])   


drop table #temp

Merge Results into one cell

1 Answers1