给你一个相似的例子(摘自txlicenhe的专栏)eg2:
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
或者是从表B变成A(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A
有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
或者是从表B变成A(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!
--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A
field2=(select convert(varchar(10),opdate,120)+'/'+opstr from t1 where opstr='aaaaaa'),
field3=(select convert(varchar(10),opdate,120)+'/'+opstr from t1 where opstr='aaaaaa')
into t2如果t2表已经存在insert into t2 select filed1=(select convert(varchar(10),opdate,120)+'/'+opstr from t1 where opstr='aaaaaa'),
field2=(select convert(varchar(10),opdate,120)+'/'+opstr from t1 where opstr='aaaaaa'),
field3=(select convert(varchar(10),opdate,120)+'/'+opstr from t1 where opstr='aaaaaa')
field2=(select convert(varchar(10),opdate,120)+'/'+opstr from t1 where opstr='bbbbbb'),
field3=(select convert(varchar(10),opdate,120)+'/'+opstr from t1 where opstr='cccccc')
into t2如果t2表已经存在insert into t2 select filed1=(select convert(varchar(10),opdate,120)+'/'+opstr from t1 where opstr='aaaaaa'),
field2=(select convert(varchar(10),opdate,120)+'/'+opstr from t1 where opstr='bbbbbb'),
field3=(select convert(varchar(10),opdate,120)+'/'+opstr from t1 where opstr='cccccc')
declare @s1 varchar(30)
set @s='select '
declare kk cursor for
select Opdate+'/'+opstr from A
open kk
fetch next from kk into @s1
while @@fetch_status=0
begin
set @s=@s+''''+@s1+''','
fetch next from kk into @s1
end
close kk
deallocate kk
set @s=substring(@s,1,len(@s)-1)
exec(@s)
----------------------------------------------------
opdate opstr
----------------------------------------------------
.... 2004-12-08 aaaaaa ....
.... 2004-12-09 bbbbbb ....
.... 2004-12-10 cccccc ....
.... 2004-12-11 dddddd ....如何在视图中实现如下效果
------------------------------------------------------------
field1 field2 field3
-------------------------------------------------------------
2004-12-08/aaaaaa 2004-12-09/bbbbbb 2004-12-10/cccccc
表A中多余3条的记录不显示在视图中。谢谢--------------------------------------------------------------
create view vew_yourview
as
select
case when opstr='aaaaaa' then opdate+'/'+'aaaaaa' end as field1,
case when opstr='bbbbbb' then opdate+'/'+'bbbbbb' end as field2,
case when opstr='cccccc' then opdate+'/'+'cccccc' end as field3
from 表A
go
給表A填入檢驗數據 可看到結果
declare @s varchar(8000),@i varchar(10)
select @s='',@i=0
set rowcount 3
select @i=@i+1,@s=@s+',[field'+@i+']='''+convert(char(10),opdate,120)+'/'+rtrim(opstr)+'''' from 表
set @s=stuff(@s,1,1,'')
exec('select '+@s)