I have a sub query that gets the top author by first name but since there can be more than 1 author, I want to concatenate the authors by ",". Would I need to use a case statement to check if there are more than 1 authors and then get the authors. How would I do that exactly?
The following is the subquery used to get the 1st top author:
Select p.pubdate, p.pccity,
(SELECT TOP (1) con.FirstName+' '+con.LastName from PrCrew pc
left join Positions pos on pos.ID=pc.Position
left join Contacts con on con.ID=pc.Contact
where pc.Production=p.ID and pc.Position='2' ORDER BY con.FirstName DESC )as [Author]
from Productions p
left join C c on c.Username=p.CS
left join Off o on o.Code=p.off
