一个比较复杂的查询，求教
三个表的关连查询
tab1.
id name
1 a
2 b

tab2.
id value1
1 a
1 NULL
1 b
2 c

tab3.
id value2
1 a
2 b
2 c

按照id做关联查询，但是需要把表1表2中具备相同id行的字段拼接成一个字符串，结果如下
id name value1 value2
1 a a,b a
2 b c b,c


[解决办法]

SQL code

if object_id('[tab1]') is not null drop table [tab1]gocreate table [tab1]([id] int,[name] varchar(1))insert [tab1]select 1,'a' union allselect 2,'b'goif object_id('[tab2]') is not null drop table [tab2]gocreate table [tab2]([id] int,[value1] varchar(1))insert [tab2]select 1,'a' union allselect 1,null union allselect 1,'b' union allselect 2,'c'goif object_id('[tab3]') is not null drop table [tab3]gocreate table [tab3]([id] int,[value2] varchar(1))insert [tab3]select 1,'a' union allselect 2,'b' union allselect 2,'c'goselect  a.id,a.name,  value1=stuff((select isnull(','+b.value1,'') from tab2 b where a.id=b.id for xml path('')),1,1,''),  value2=stuff((select isnull(','+c.value2,'') from tab3 c where a.id=c.id for xml path('')),1,1,'')from  tab1 agroup by   a.id,a.name/**id          name value1                                             value2----------- ---- -------------------------------------------------- -----------------------------1           a    a,b                                                a2           b    c                                                  b,c(2 行受影响)**/