话不多说,先上表结构和数据
委托人表:
SELECT [F_Id]
,[F_ReportId]
,[F_Name]
FROM [F_Client_Info]
表数据及结构如下图所示:
项目表:
SELECT [F_Id]
,[F_ReportNo]
,[F_ReportName]
FROM [Report_Info]
表数据及结构如下图所示:
两表联查
查询语录初始版本:
SELECT r.[F_Id] as F_ReportId ,r.[F_ReportNo]
,(SELECT [F_Name] FROM [F_Client_Info] c where c.[F_ReportId] = r.F_Id)as ClientName
FROM [Report_Info] as r
查询结果:
后经过查询得以解决,具体代码如下所示:
SELECT r.[F_Id] as F_ReportId ,r.[F_ReportNo]
--,(SELECT [F_Name] FROM [F_Client_Info] c where c.[F_ReportId] = r.F_Id)as ClientName
,stuff((select ','+[F_Name] FROM [F_Client_Info] c where c.[F_ReportId] = r.F_Id for xml Path('')),1,1,'') as ClientName
FROM [Report_Info] as r
查询结果:
参考:https://www.cnblogs.com/xiaobaidejiucuoben/p/10221048.html