CODEWITHSUNDEEP
sql-server-2008csv
asma
asma

Reputation: 2825

How to Comma separate multiple rows obtained from a SQL Query

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:

enter image description here

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?

Upvotes: 1

Views: 8473

Answers (6)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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

Upvotes: 0

asma
asma

Reputation: 2825

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

Upvotes: 3

JStead
JStead

Reputation: 1730

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

Upvotes: 0

Shan Plourde
Shan Plourde

Reputation: 8706

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

Upvotes: 1

user703946
user703946

Reputation:

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...

Upvotes: 0

A. L. Flanagan
A. L. Flanagan

Reputation: 1168

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?

Upvotes: 0

Related Questions