如何用逗号分隔从 SQL 查询获得的多行

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

它返回这些行：

正如您所看到的，第一列具有所有相同的行，因为一名员工可以拥有多个头衔。

如何将每个员工的所有头衔合并为逗号分隔值，以便每个员工只有一行？

Answer 1

这对我有用：

;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

Answer 2

我通常创建 SQL 函数并从查询中调用它们。您可以使用多种方法创建逗号分隔的值列表，请参阅 http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list -using-select-clause-from-table-column/，我可以用逗号将多行分隔成一列吗？。

在这种情况下，如果您创建一个函数，则可以从 SQL 查询中调用它。像这样的东西：

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

Answer 3

这是在标准 SQL 中很难（或不可能）做到的事情之一。许多供应商已经扩展了 SQL 以支持这样的“汇总”；不幸的是，每个供应商的语法都不同。抱歉，我目前没有方便的示例。那么您使用的是什么 DBMS？

Answer 4

尝试这样的事情：

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

无法真正测试，所以它很困难，尝试和错误通常最终会起作用。不过，我建议按唯一的 id 进行分组。

我希望这有帮助...

Answer 5

您可以使用此查询。我有针对遵循此模式的此类查询的通用模板。您可能必须对此进行调试，因为我没有您的表结构。它将标题合并到一个列表中

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

Answer 6

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