----表结构和测试数据
CREATE TABLE [tt] (
[key] [varchar(20)],
[cid] [text],---内容,有整数和","组成。
[hz] [text] ---权重,每一个内容对应有一个权重。
)
GO
INSERT tt VALUES('s','1,2,3,4','9.0,7.8,12.9,45.8')
INSERT tt VALUES('s','5,6','9.8,12.8')
INSERT tt VALUES('s','78,21','32.8,56.7')需要结果:
tt cid hz
s 1,2,3,4,5,6,78,21 9.0,7.8,12.9,45.8,9.8,12.8,32.8,56.7
CREATE TABLE [tt] (
[key] [varchar(20)],
[cid] [text],---内容,有整数和","组成。
[hz] [text] ---权重,每一个内容对应有一个权重。
)
GO
INSERT tt VALUES('s','1,2,3,4','9.0,7.8,12.9,45.8')
INSERT tt VALUES('s','5,6','9.8,12.8')
INSERT tt VALUES('s','78,21','32.8,56.7')需要结果:
tt cid hz
s 1,2,3,4,5,6,78,21 9.0,7.8,12.9,45.8,9.8,12.8,32.8,56.7
declare @cid nvarchar(200)
declare @hz nvarchar(200)
set @cid = ''
set @hz =''
select @cid = @cid + [cid], @hz = @hz + hz from tt
select @cid ,@hz
CREATE TABLE [tt] (
[key] varchar(20),
[cid] text,
[hz] text
)
GO
INSERT tt VALUES('s','1,2,3,4','9.0,7.8,12.9,45.8')
INSERT tt VALUES('s','5,6','9.8,12.8')
INSERT tt VALUES('s','78,21','32.8,56.7') create function f_cid (@key varchar(20))
returns nvarchar(2000)
as
begin
declare @sql nvarchar(4000)
select @sql=isnull(@sql+',','')+cast(cid as nvarchar(2000))from tt where [key]=@key
return (@sql)
end
go
create function f_hz (@key varchar(20))
returns nvarchar(2000)
as
begin
declare @sql nvarchar(4000)
select @sql=isnull(@sql+',','')+cast(hz as nvarchar(2000)) from tt where [key]=@key
return (@sql)
end
goselect distinct [key],dbtest.dbo.f_cid([key]) as cid, dbtest.dbo.f_hz([key]) as hz from ttdrop function f_test
drop function f_hz
--drop table tt
[key] varchar(20),
[cid] [text],
[hz] [text]
)
go
create TABLE #tab (
[key] varchar(20),
[cid] [text],
[hz] [text]
)
go
INSERT #tt VALUES('s','1,2,3,4','9.0,7.8,12.9,45.8')
INSERT #tt VALUES('s','5,6','9.8,12.8')
INSERT #tt VALUES('s','78,21','32.8,56.7')
go
declare @key varchar(10)
declare @cid varchar(200)
declare @hz varchar(200)
declare curSumOfString cursor static for
select [key] from #tt group by [key]
open curSumOfString
fetch next from curSumOfString into @key
while @@fetch_status = 0
begin
set @cid = ''
set @hz =''
select @cid = @cid + convert(varchar(20),cid), @hz = @hz + convert(varchar(20),hz) from #tt where [key] = @key
insert into #tab([key],cid,hz)values(@key, @cid ,@hz)
fetch next from curSumOfString into @key
end
close curSumOfString
deallocate curSumOfStringselect * from #tab
drop function f_cid
drop function f_hz
--drop table tt
dbtest.dbo.f_cid数据库:dbtest
所有者:dbo
函数名:f_cid