create function getstr(@content int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+rtrim(b2) from TableB where b1=@content
select @str=right(@str,len(@str)-1) where @str<>''
return @str
end
go--调用:
select a1,a2,dbo.getstr(a1) b2 from Tablea
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+rtrim(b2) from TableB where b1=@content
select @str=right(@str,len(@str)-1) where @str<>''
return @str
end
go--调用:
select a1,a2,dbo.getstr(a1) b2 from Tablea
解决方案 »
- 请问如何统计当月加班时数
- 有专门介绍数据库连接池或数据库性能测试的书籍没
- 用字符串表示日期的比较和累加
- 大件事了! 关于恢复数据库问题
- 真的很急 急求~~~高分求高手 求一SQL算法 有思路 但写不出T-SQL 帮忙~
- select * from iVR_haoduan where a<='13722222222' and b>='13711111111'
- 请问,用什么样的方法来显示并修改修改表中的<long text>?
- sql2005 count求和where后带like异常慢,求支招!
- 疑难问题,请求帮助!!!
- 求助!关于非当月库存的查讯问题!
- 关于Delphi调用SQL Server的dll的问题
- 请教各路英雄,如何在把自定义的视频文件写入字段中?(在线等待)
设
A表内容 B表
1 0 1 a1
2 0 1 a2
2 b1
2 b2
2 b3要求结果:
1 a1 a2
2 b1 b2 b3
则处理方法如下:
--为数据处理准备临时表
select id=identity(int,0,1),b1,b2 into #tb from @B where b1 in(select a1 from @A)--生成数据处理语句
declare @sql varchar(8000),@i int,@j varchar(50)
select @sql='',@i=aa
,@j=',max(case id % '+cast(aa as varchar)+' when '
from(select top 1 b1,count(b1) as aa from #tb group by b1 order by aa desc) a
while @i>0
select @sql=@j+cast(@i-1 as varchar)+' then b2 else '''' end) as ['
+cast(@i-1 as varchar)+']'+@sql,@i=@i-1
set @sql='select b1'+@sql+' from #tb group by b1'--执行处理语句,得到结果
exec(@sql)--删除临时表
drop table #tb
declare @A Table(a1 int IDENTITY(1,1),a2 int)
insert into @A
select 0
union all select 1
union all select 2declare @B Table(b1 int,b2 varchar(50))
insert into @B
select 1,'a1'
union all select 1,'a2'
union all select 2,'b1'
union all select 2,'b2'
union all select 2,'b3'--为数据处理准备临时表
select id=identity(int,0,1),b1,b2 into #tb from @B where b1 in(select a1 from @A)--生成数据处理语句
declare @sql varchar(8000),@i int,@j varchar(50)
select @sql='',@i=aa
,@j=',max(case id % '+cast(aa as varchar)+' when '
from(select top 1 b1,count(b1) as aa from #tb group by b1 order by aa desc) a
while @i>0
select @sql=@j+cast(@i-1 as varchar)+' then b2 else '''' end) as ['
+cast(@i as varchar)+']'+@sql,@i=@i-1
set @sql='select b1'+@sql+' from #tb group by b1'--执行处理语句,得到结果
exec(@sql)--删除临时表
drop table #tb
/*-------------------------------------------------
上面语句的测试结果:b1 1 2 3
----------- ---------- ---------- ----------
1 a1 a2
2 b2 b3 b1
------------------------------------------------*/
insert into @A
select 0
union all select 1
union all select 2declare @B Table(b1 int,b2 varchar(50))
insert into @B
select 1,'a1'
union all select 1,'a2'
union all select 2,'b1'
union all select 2,'b2'
union all select 2,'b3'--为数据处理准备临时表
select id=identity(int,0,1),b1,b2 into #tb from @B where b1 in(select a1 from @A)--生成数据处理语句
declare @sql varchar(8000),@i int,@j varchar(50)
select @sql='',@i=aa
,@j=',max(case id % '+cast(aa as varchar)+' when '
from(select top 1 b1,count(b1) as aa from #tb group by b1 order by aa desc) a
while @i>0
select @sql=@j+cast(@i-1 as varchar)+' then b2 else '''' end) as ['
+cast(@i as varchar)+']'+@sql,@i=@i-1
set @sql='select b1'+@sql+' from #tb group by b1'--执行处理语句,得到结果
exec(@sql)--删除临时表
drop table #tb
/*-------------------------------------------------
上面语句的测试结果:b1 1 2 3
----------- ---------- ---------- ----------
1 a1 a2
2 b2 b3 b1
------------------------------------------------*/