How to Comma separate multiple rows obtained from a SQL Query

Question

Select s.FirstName, glTitle.LookupItem Title
    from ClientStaff cs 
    left outer join Staff s on s.Id = cs.StaffId 
    left outer join StaffTitle st on st.StaffId = s.Id 
    left outer join GeneralLookup glTitle on glTitle.Id = st.glTitleId

It returns these rows:

As you can see, the first column has all same rows because one employee can have multiple titles.

How can I merge all of the titles for each employee into a comma separated value so that there is only one row per employee?

Have a look at this answer http://stackoverflow.com/questions/5631899/t-sql-how-to-get-the-corresponding-comma-separated-text-for-a-string-of-comma-s/5633449#5633449. The cte part splits a comma separated string to rows and the query using the cte is combining them back again. — Mikael Eriksson, Apr 12 '11 at 13:06

score 3 · Accepted Answer · answered Apr 14 '11 at 05:37

This worked for me:

;with mycte as
    (select s.FirstName, glTitle.LookupItem Title 
    from ClientStaff cs  
    left outer join Staff s on s.Id = cs.StaffId  
    left outer join StaffTitle st on st.StaffId = s.Id  
    left outer join GeneralLookup glTitle on glTitle.Id = st.glTitleId
    group by FirstName, glTitle.LookupItem)

    SELECT Distinct FirstName, Titles = Replace(Replace(( SELECT title  AS [data()] FROM mycte a

    WHERE a.FirstName = b.FirstName 

    ORDER BY a.title FOR XML PATH  ),'</row>',', '),'<row>','')

    FROM mycte b
    ORDER BY FirstName

score 1 · Answer 2 · edited May 23 '17 at 12:07

1

I usually create SQL functions and call them from my queries. You can create a comma delimited list of values using a multitude of approaches, see http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/, Can I Comma Delimit Multiple Rows Into One Column?.

In this case if you create a function, you can then call it from your SQL query. Something like:

select s.FirstName, dbo.GetAllJobTitlesForStaff(s.Id) AS AllJobTitles
from ClientStaff cs 
left outer join Staff s on s.Id = cs.StaffId

edited May 23 '17 at 12:07

Community

1
1

answered Apr 12 '11 at 12:53

Shan Plourde

8,528
2
29
42

first one is a nice link !! But it can't return 2 columns. It is best for 1 column – asma Apr 14 '11 at 13:41

score 0 · Answer 3 · answered Apr 12 '11 at 12:35

This is one of those things that's difficult (or impossible) to do in standard SQL. Many vendors have extended SQL to support "rollups" like that; unfortunately, each vendor's syntax is different. Sorry, I don't have an example handy at the moment. So what DBMS are you using?

score 0 · Answer 4 · answered Apr 12 '11 at 12:52

Try something like:

select s.FirstName, GROUP_CONCAT(glTitle.LookupItem Title, ',')
from ClientStaff cs 
left outer join Staff s on s.Id = cs.StaffId 
left outer join StaffTitle st on st.StaffId = s.Id 
left outer join GeneralLookup glTitle on glTitle.Id = st.glTitleId
GROUP BY s.FirstName

Cant really test, so its difficult, trail and error usually works eventually. I would suggest to group by a unique id though.

I hope this helps...

I wish SQL Server had GROUP_CONCAT. That mysql function is amazing! — JStead, Apr 12 '11 at 13:03

score 0 · Answer 5 · answered Apr 12 '11 at 12:59

You can use this query. I have generic template for queries like these that follows this pattern. You might have to debug this because I don't have your table structure. It coalesces the titles into a list

DECLARE @List varchar(2000), @otherList varchar(2000),@FirstName varchar(2000),@id varchar(2000)
declare @temp table(
    firstName varchar(128),
    title varchar(4000)
)

DECLARE TitleList CURSOR FAST_FORWARD  FOR
select s.FirstName,  s.Id  
from ClientStaff cs  
left outer join Staff s on s.Id = cs.StaffId
OPEN TitleList
FETCH NEXT FROM TitleList INTO @FirstName,@id
WHILE @@FETCH_STATUS = 0
BEGIN
    select @List = COALESCE(@List + ',', '') + Cast(glTitle.LookupItem As varchar(400))
    from StaffTitle st  
    left outer join GeneralLookup glTitle on glTitle.Id = st.glTitleId 
    where st.StaffId = id

    insert into @temp
    select @FirstName,@List
    set @List = null;
FETCH NEXT FROM TitleList INTO @FirstName,@id
END
CLOSE TitleList
DEALLOCATE TitleList

select * from @temp

Your query is not working fine :( it returns 2,2, rows for each first name and titles are same for all rows — asma, Apr 13 '11 at 06:43
what are the relationships between those tables ie 1-M, 1-1, 0-M — JStead, Apr 13 '11 at 12:02

Mikael Eriksson · Answer 6 · 2011-04-14T11:01:38.957

0

select
  s.FirstName,
  stuff((select ', '+glTitle.LookupItem
         from StaffTitle as st
           inner join GeneralLookup as glTitle
             on glTitle.Id = st.glTitleId 
         where st.StaffId = s.Id
         for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') as Title
from Staff as s

edited Apr 14 '11 at 11:01

answered Apr 14 '11 at 05:51

Mikael Eriksson

136,425
22
210
281

it is repeating the records. Working very fine but rows are duplicated. Any solution? – asma Apr 14 '11 at 10:27
@asma – Try to remove the join with `ClientStaff`. Let me know if it works or if ClientStaff table is necessary for your query. If it is you can add that in another way. – Mikael Eriksson Apr 14 '11 at 11:03
@asma you don't use. What do you want from that table? – Mikael Eriksson Apr 14 '11 at 13:03
I have client id in that table. In this query I am not using but in actual, I have to use. – asma Apr 14 '11 at 13:41
@asma Since you have more than one row in `ClientStaff` for each row in `Staff` you will have duplicates of Staff in the output. I have showed you the technique to concatenate strings from multiple rows. I don't know what you need to do to get your expected output when joining to other tables since I don't know what you want or what your data looks like. – Mikael Eriksson Apr 14 '11 at 14:10

How to Comma separate multiple rows obtained from a SQL Query

6 Answers6