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