我有两张表分别是T_Lab_FileInformation(文件信息表)和T_Sys_Orgs(部门表)我文件信息表中这个字段ScopeIissue存储的是部门表的主键(Org_Id),我现在查询文件信息表时间我想查出来比如：
这条数据我想查出来每条数据中都对应哪些部门....1002,1003这应该是对应两个部门的...我要怎么写查询语句呀
求高手帮忙呀！急用啊~高分求助啊------解决思路----------------------
给你个例子吧 没有元数据
with cte as
(select 'A' as 客户,'H1' as 医院 union all
select 'A' as 客户,'H2' as 医院 union all
select 'B' as 客户,'H3' as 医院 union all
select 'C' as 客户,'H4' as 医院 union all
select 'C' as 客户,'H5' as 医院 union all
select 'C' as 客户,'H6' as 医院)
select a.客户,
stuff((select ','+a.医院 from cte as b where b.客户=a.客户 for xml path('')),1,1,'') as 医院
from cte as a
GROUP BY A.客户
------解决思路----------------------
with cte as
(select 'A' as 客户,'H1' as 医院 union all
select 'A' as 客户,'H2' as 医院 union all
select 'B' as 客户,'H3' as 医院 union all
select 'C' as 客户,'H4' as 医院 union all
select 'C' as 客户,'H5' as 医院 union all
select 'C' as 客户,'H6' as 医院)
select a.客户,
stuff((select ','+b.医院 from cte as b where b.客户=a.客户 for xml path('')),1,1,'') as 医院
from cte as a
GROUP BY A.客户
--结果
客户 医院
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A H1,H2
B H3
C H4,H5,H6
(3 行受影响)
楼上代码写错了。