mssql行转死

select
b.Name as CompanyName,
c.Name as DepartmentName,
t.Name as Phase,
stuff((select ','+cast(Name as varchar) from Users where userid in (select
userid
from CaseForUser tc where Caseid=a.caseid) for xml path('')),1,1,'') as AgentNames
,
stuff((select ','+cast(Name as varchar) from UndertakingOffices where UndertakingOfficeId in (select
UndertakingOfficeId
from CaseForUndertakingOffice tcc where tcc.Caseid=a.caseid) for xml path('')),1,1,'') as OfficeNames
,cr.ReversedCaseId,cr1.Case_Id,
a.* from cases a
left join Departments b
on a.CompanyId=b.DepartmentId
left join Departments c
on c.DepartmentId=a.DepartmentId
LEFT JOIN CaseTypes t on t.CaseTypeId=a.caseprocedure
LEFT JOIN CaseReversed cr on cr.Case_Id=a.caseid
left join CaseReversed cr1 on cr1.ReversedCaseId=a.caseid
where a.casedelete is null