求一个一对多sql查询－－－在线等！！

[lmdotnet]

表Table1
id,opp,description
1, '监控系统'，'大项目'
2, '发电系统'，'大项目'

表Table2
id account,type
1,'名字1','设计方1'
1,'名字2','设计方2'
1,'名字2','甲方1'
2,'名字A','设计方A'
2,'名字B','甲方A'
2,'名字C','系统集成商A'
2,'名字D','系统集成商B'
2,'名字E','系统集成商C'

表Table1与表Table2是1对多的关系，通过id关联

通过sql查出：

id, opp , 系统集成商, 设计方, 甲方
1, '监控系统'， 'NULL', '设计方1|设计方2', '甲方1'
2, '发电系统'， '系统集成商A|系统集成商B|系统集成商C', '设计方A', '甲方A'

在线等，多谢了！！

[chuifengde]

CREATE TABLE a_(id int,opp VARCHAR(20),DESCRIPTION VARCHAR(20))
insert a_ select 1, '监控系统','大项目'
union all select 2, '发电系统','大项目'
GO 
CREATE TABLE  b_ (id INT, account  VARCHAR(20),TYPE VARCHAR(20))
insert b_ select 1,'名字1','设计方1'
union all select 1,'名字2','设计方2'
union all select 1,'名字2','甲方1'
union all select 2,'名字A','设计方A'
union all select 2,'名字B','甲方A'
union all select 2,'名字C','系统集成商A'
union all select 2,'名字D','系统集成商B'
union all select 2,'名字E','系统集成商C'

go 
CREATE FUNCTION get_V_(@id INT,@F VARCHAR(20))
RETURNS VARCHAR(1000)
BEGIN
	DECLARE @sql VARCHAR(8000)
	
	SELECT @sql=ISNULL(@sql+'|','')+TYPE FROM b_ 
	WHERE id=@id and CHARINDEX(@f,TYPE)>0
	RETURN @sql 	
END

GO 
SELECT DISTINCT a.*,dbo.get_V_(a.id,'系统集成商') 系统集成商,
dbo.get_V_(a.id,'设计方') 设计方,
dbo.get_V_(a.id,'甲方') 甲方
 FROM a_ a INNER JOIN b_ b
ON a.id=b.id
--result
/*id          opp                  DESCRIPTION          系统集成商                                                                                                                                                                                                                                                                                                        设计方                                                                                                                                                                                                                                                                                                          甲方                                                                                                                                                                                                                                                                                                           
----------- -------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
1           监控系统                 大项目                  NULL                                                                                                                                                                                                                                                                                                         设计方1|设计方2                                                                                                                                                                                                                                                                                                    甲方1
2           发电系统                 大项目                  系统集成商A|系统集成商B|系统集成商C                                                                                                                                                                                                                                                                                         设计方A                                                                                                                                                                                                                                                                                                         甲方A

（所影响的行数为 2 行）*/

[永生天地]

有点难转

[jaydom]

use PracticeDB
go
if exists (select 1 from sysobjects where name ='tb_a')
drop table tb_a
go
create table tb_a(id int, opp varchar(10),description varchar(10) )
insert into tb_a 
select 1, '监控系统','大项目' union all
select 2, '发电系统','大项目'
go
if exists (select 1 from sysobjects where name ='tb_b')
drop table tb_b
go
create table tb_b(id int, account varchar(10),type varchar(50))
insert into tb_b
select 1,'名字1','设计方1' union all
select 1,'名字2','设计方2' union all
select 1,'名字2','甲方1' union all
select 2,'名字A','设计方A' union all
select 2,'名字B','甲方A' union all
select 2,'名字C','系统集成商A' union all
select 2,'名字D','系统集成商B' union all
select 2,'名字E','系统集成商C'


select * from tb_a
select * from tb_b


;with t
as
(
	select id,case when type like '系统集成商%' then type else null end as 系统集成商,
							  case when type like '设计方%' then type else null end as 设计方,
							  case when type like '甲方%' then type else null end as 甲方
	from tb_b
),t1
as
(
	select id , replace(stuff((select '|',isnull(系统集成商,0)
					   from t 
					   where  id=a.id for xml path('')),1,1,''),'0|','')系统集成商,
				replace(replace(stuff((select '|',isnull(设计方,0)
					   from t 
					   where  id=a.id for xml path('')),1,1,''),'0|',''),'|0','')设计方,
				replace(replace(stuff((select '|',isnull(甲方,0)
					   from t 
					   where  id=a.id for xml path('')),1,1,''),'0|',''),'|0','')甲方
				  
	from t a
	group by id
)
select tb_a.id,opp,REPLACE(t1.系统集成商,'0','null')系统集成商,t1.设计方,t1.甲方
from tb_a join t1 on tb_a.id=t1.id

id	opp	系统集成商	设计方	甲方
1	监控系统	null	设计方1|设计方2	甲方1
2	发电系统	系统集成商A|系统集成商B|系统集成商C	设计方A	甲方A