Table AID Desc
1 test1
2 test2Table BAID SEQ DESC
1 1 B1
1 2 B2
1 3 B3
2 1 B4
2 2 B5A和B 是一对多的关系
要得到
1 test1 B1 B2 B3
2 test2 B4 B5
1 test1
2 test2Table BAID SEQ DESC
1 1 B1
1 2 B2
1 3 B3
2 1 B4
2 2 B5A和B 是一对多的关系
要得到
1 test1 B1 B2 B3
2 test2 B4 B5
(replace((select ','+DESC from B where AID=A.ID for xml path('')),',',' ')) as desc
from A
left join B b on a.Id=b.Iddeclare @a int,@b varchar(200)
select @a=0,@b=''
update #temp set @b=Desc2=case when @a=ID then @b+','+Desc2 else Desc2 end,@a=IDselect ID,Desc,max(Desc2) as Desc2 from #temp group by ID,Desc
,[Team]=(select ' '+[Desc] from B where ID=AID for xml path(''))
from (select ID,A.[Desc],AID from A inner join B on ID=AID )T
sql2000 一条语句查询出来
想了行转列 但是是字符型的 没办法sum
DECLARE @A table(ID int,[desc] varchar(10))
DECLARE @B table(AID int,SEQ int ,[DESC] varchar(10))
insert into @A
select 1,'test1' union all
select 2,'test2'
insert into @B
select 1,1,'B1' union all
select 1,2,'B2' union all
select 1,3,'B3' union all
select 2,1,'B4' union all
select 2,2,'B5'select A.*,
(replace((select ','+[DESC] from @B where AID=A.ID for xml path('')),',',' ')) as [desc]
from @A a/*ID desc desc
----------- ---------- --------
1 test1 B1 B2 B3
2 test2 B4 B5(2 行受影响)*/
INSERT aa_ SELECT 1 ,'test1'
union all SELECT 2 ,'test2'CREATE TABLE bb_(AID INT, SEQ INT, [DESC] VARCHAR(20))
INSERT bb_ SELECT 1, 1 ,'B1'
UNION ALL SELECT 1, 2 ,'B2'
UNION ALL SELECT 1, 3 ,'B3'
UNION ALL SELECT 2, 1 ,'B4'
UNION ALL SELECT 2, 2 ,'B5'
--分列放
DECLARE @sql VARCHAR(8000)
SELECT @sql=ISNULL(@sql+',','')+'max(case when seq='+LTRIM(seq)+' then b.[desc] else '''' end) ['+LTRIM(seq)+']'
FROM (SELECT DISTINCT seq FROM bb_)aaEXEC('select a.id,a.[desc],'+@sql+' from aa_ a inner join bb_ b on a.id=b.aid group by a.id,a.[desc]')--result
/*id desc 1 2 3
----------- -------------------- -------------------- -------------------- --------------------
1 test1 B1 B2 B3
2 test2 B4 B5 */
go
--一列合并放
CREATE FUNCTION Get_aa_bb_(@id INT )
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @sql VARCHAR(8000)
SELECT @sql=ISNULL(@sql+' ','')+[Desc] FROM bb_ WHERE aid=@id ORDER BY seq
RETURN @sql
END
go
SELECT a.*,dbo.Get_aa_bb_(a.id) re FROM aa_ a
--result
/*ID DESC re
----------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 test1 B1 B2 B3
2 test2 B4 B5(所影响的行数为 2 行)
*/
drop table A
create table A
(
ID int,
[Desc] varchar(10)
)
go
insert into A(ID,[Desc])
(
select 1,'test1' union
select 2,'test2'
)
go
if exists(select * from sysobjects where [name]='B')
drop table B
create table B
(
AID int,
SEQ int,
[Desc] varchar(10)
)
go
insert into B(AID,SEQ,[Desc])
(
select 1,1,'B1' union
select 1,2,'B2' union
select 1,3,'B3' union
select 2,1,'B4' union
select 2,2,'B5'
)
go
select ID =BB.ID,[Desc]
, Desc1 = stuff((select ',' + Convert(varchar(10),Desc1)
from (select A.ID,A.[Desc],B.[Desc] Desc1 from A,B where A.ID=B.AID) AA
where AA.ID =BB.ID for xml path('')),1,1,'')
from (select A.ID,A.[Desc],B.[Desc] Desc1 from A,B where A.ID=B.AID) BB
group by ID,[Desc]
DECLARE @A table(ID int,[desc] varchar(10))
DECLARE @B table(AID int,SEQ int ,[DESC] varchar(10))
insert into @A
select 1,'test1' union all
select 2,'test2'
insert into @B
select 1,1,'B1' union all
select 1,2,'B2' union all
select 1,3,'B3' union all
select 2,1,'B4' union all
select 2,2,'B5'select a.*,
max(case b.SEQ when 1 then b.[desc] else '' end ) col1,
max(case b.SEQ when 2 then b.[desc] else '' end ) col2,
max(case b.SEQ when 3 then b.[desc] else '' end ) col3
from @A a inner join @B b on(a.ID=b.AID) group by a.ID,a.[desc]/*
ID desc col1 col2 col3
----------- ---------- ---------- ---------- ----------
1 test1 B1 B2 B3
2 test2 B4 B5 (2 行受影响)*/
要动态的话,看十楼的。
max(case b.SEQ when 1 then b.[desc] else '' end ) col1,
max(case b.SEQ when 2 then b.[desc] else '' end ) col2,
max(case b.SEQ when 3 then b.[desc] else '' end ) col3
from @A a inner join @B b on(a.ID=b.AID) group by a.ID,a.[desc]