求SQL语句！有点难度哦！！

[swazn_yj]

CREATE TABLE [dbo].[Sw_users](
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [varchar](50) NULL,
[name] [varchar](50) NULL,
CONSTRAINT [PK_Sw_users] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

insert into Sw_users(username,name) values('zhangshan','张三')
insert into Sw_users(username,name) values('lisi','李四')
insert into Sw_users(username,name) values('wangwu','王五')
insert into Sw_users(username,name) values('liudehua','刘德华')
insert into Sw_users(username,name) values('zhangxueyou','张学友')

CREATE TABLE [dbo].[md_dept](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NULL,
[director] [varchar](20) NULL,
CONSTRAINT [PK_2015_dept] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)

ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

inert into md_dept(name,director) values('市场部','zhangshan,lisi')
inert into md_dept(name,director) values('娱乐部','zhangxueyou,liudehua')
inert into md_dept(name,director) values('财务部','wangwu')
inert into md_dept(name,director) values('综合部','liudehua')

select a.*,b.name as pname from md_dept a left join sw_users b on CHARINDEX(','+b.username+',',','+a.director+',')>0 where a.ID>0

我想要的格式是这样的：
name director panme
市场部 zhangshan,lisi 张三，李四
娱乐部 zhangxueyou,liudehua 张学友,刘德华
财务部 wangwu 王五
综合部 liudehua 刘德华



实际输出是这样的：
name director panme
市场部 zhangshan,lisi 张三
市场部 zhangshan,lisi 李四
娱乐部 zhangxueyou,liudehua 张学友
娱乐部 zhangxueyou,liudehua 刘德华
财务部 wangwu 王五
综合部 liudehua 刘德华

[swazn_yj]

不知道我说清楚了没，就是一个部门有两个主管的时候，我只想查出一列把这两个人的名字都输出，因为部门表里存的主管是用户名（名字有重复的情况），所以得从用户表里调出这个用户名对应的姓名。实际查的时候会查出两列，不好处理，请哪位大哥帮忙搞定一下，谢谢！

[还在加载中灬]

select a.*
	,STUFF((SELECT ','+b.name FROM sw_users b WHERE CHARINDEX(','+b.username+',',','+a.director+',')>0 FOR XML PATH('')),1,1,'') as pname
from md_dept a where a.ID>0

不知道这样可不可以

[reenjie]

with cte as ( select a.*,b.name as pname from md_dept a left join sw_users b on CHARINDEX(','+b.username+',',','+a.director+',')>0 where a.ID>0 ) select name,director,(stuff(select ','+pname from cte where name=c.name and director=c.director,1,1,'')) as pname from cte as c group by name,director

[reenjie]

引用 3 楼 reenjie 的回复:

with cte as ( select a.*,b.name as pname from md_dept a left join sw_users b on CHARINDEX(','+b.username+',',','+a.director+',')>0 where a.ID>0 ) select name,director,(stuff(select ','+pname from cte where name=c.name and director=c.director,1,1,'')) as pname from cte as c group by name,director

小調一下 with cte as ( select a.*,b.name as pname from md_dept a left join sw_users b on CHARINDEX(','+b.username+',',','+a.director+',')>0 where a.ID>0 ) select name,director,(stuff(select ','+pname from cte where name=c.name and director=c.director for xml(''),1,1,'')) as pname from cte as c group by name,director

[还在加载中灬]

如果对pname中的名字有顺序的要求，还可以这样

select a.*
	,STUFF((SELECT ','+b.name FROM sw_users b WHERE CHARINDEX(','+b.username+',',','+a.director+',')>0  ORDER BY CHARINDEX(','+b.username+',',','+a.director+',') FOR XML PATH('')),1,1,'') as pname
from md_dept a where a.ID>0

[swazn_yj]

引用 2 楼 ky_min 的回复:

select a.*
	,STUFF((SELECT ','+b.name FROM sw_users b WHERE CHARINDEX(','+b.username+',',','+a.director+',')>0 FOR XML PATH('')),1,1,'') as pname
from md_dept a where a.ID>0

不知道这样可不可以

这样就OK了！

[swazn_yj]

感谢楼上几位治好了困扰我多年的老便秘！

[KeepSayingNo]

你看看我的文章会对你的“便秘”有更好的疗效，开玩笑的，请看看我的博文，里面就是针对你这个问题写的 http://blog.csdn.net/dotnetstudio/article/details/17008693