我试图让这个视图查询两个表,然后将每个程序ID汇总到一行,其中所有AttributeNames都在AttributeNames列中
我加入了这两个表,它提取了适当数量的记录.
现在,我需要的部分就是将每个ProgramID和一个所有AttributeNames一起放在每个id的AttributeNames列中.
示例：全部在一行中.
ProgramID | AttributeNames 887 | Studydesign,Control Groups,Primary Outcomes.
查询：
SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID,dbo.tblProgramAttributes.AttributeID AS PAattributeID,dbo.tblAttributes.AttributeID,dbo.tblAttributes.AttributeName
FROM dbo.tblProgramAttributes INNER JOIN
dbo.tblAttributes
ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID
WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%')
ORDER BY dbo.tblProgramAttributes.ProgramID DESC
解决方法
select ProgramId,stuff(
(
select ','+ [attributename]
from Table1
where programid = t.programid for XML path('')
),1,'') as AttributeNames
from (select distinct programid
from Table1 )t
看看我的sql fiddle
结果
PROGRAMID ATTRIBUTENAMES 887 Study Design,Primary Outcomes
