[求助]SQL字段重復相加問題

写函数,自己找找相关的帖子,好象精华帖里面就有

A to B

表A

1 2
1 3
2 1
2 2
3 1
表B
id pid
1 1,2,3
2 1,2
3 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

第二问
if exists(select * from sysobjects where xtype='u' and name='A_mcn') drop table A_mcn
create table A_mcn (in_detail_id char(10),scheduler_code char(6),pmat_id char(10),Jan float,Feb float,Mar float,Apr float)

declare @in_detail_id char(10)
declare @scheduler_code char(6)
declare @scheduler_month char(10)
declare @pmat_id char(10)
declare @aty_need float

declare mycur cursor for

select in_detail_id,scheduler_code,scheduler_month,pmat_id,aty_need from A_mc order by in_detail_id

open mycur

fetch next from mycur
into @in_detail_id,@scheduler_code,@scheduler_month,@pmat_id,@aty_need

while @@FETCH_STATUS = 0
begin
if @scheduler_month='1'
begin
insert into A_mcn values(@in_detail_id,@scheduler_code,@pmat_id,@aty_need,'','','')
end

if @scheduler_month='2'
begin
insert into A_mcn values(@in_detail_id,@scheduler_code,@pmat_id,'',@aty_need,'','')
end

if @scheduler_month='3'
begin
insert into A_mcn values(@in_detail_id,@scheduler_code,@pmat_id,'','',@aty_need,'')
end

if @scheduler_month='4'
begin
insert into A_mcn values(@in_detail_id,@scheduler_code,@pmat_id,'','','',@aty_need)
end

fetch next from mycur
into @in_detail_id,@scheduler_code,@scheduler_month,@pmat_id,@aty_need
end

close mycur

deallocate mycur

建了个新表，名字改一下，由于用了float，没有数据的地方全是0，改成varchar应该可以了，月份增加的话自己加相应的代码。。。

[此贴子已经被作者于2007-6-7 17:41:47编辑过]

if object_id('test') is not null
drop table test
go
create table test(in_detail_id int,scheduler_code varchar(15),scheduler_month int,pat_id varchar(15),aty_need decimal(10,4))
insert into test select 68,'Scheduler001',1,'Y005000033',6.0000
union all select 69,'Scheduler001',1,'SF20512',0.0000
union all select 70,'Scheduler001',1,'P005000010',0.0000
union all select 71,'Scheduler001',2,'P012000035',22.5000
union all select 72,'Scheduler001',2,'Y012000858',22.5000
union all select 73,'Scheduler001',3,'SF21936',0.0000
union all select 74,'Scheduler001',3,'P001000016',23.5200
union all select 75,'Scheduler001',3,'Y001000015',10.0800
go
select in_detail_id,scheduler_code,pat_id,case scheduler_month when 1 then cast(aty_need as varchar) else ''end as '1月',
case scheduler_month when 2 then cast(aty_need as varchar) else ''end as '2月',
case scheduler_month when 3 then cast(aty_need as varchar) else ''end as '3月',
case scheduler_month when 4 then cast(aty_need as varchar) else ''end as '4月',
case scheduler_month when 5 then cast(aty_need as varchar) else ''end as '5月',
case scheduler_month when 6 then cast(aty_need as varchar) else ''end as '6月',
case scheduler_month when 7 then cast(aty_need as varchar) else ''end as '7月',
case scheduler_month when 8 then cast(aty_need as varchar) else ''end as '8月',
case scheduler_month when 9 then cast(aty_need as varchar) else ''end as '9月',
case scheduler_month when 10 then cast(aty_need as varchar) else ''end as '10月',
case scheduler_month when 11 then cast(aty_need as varchar) else ''end as '11月',
case scheduler_month when 12 then cast(aty_need as varchar) else ''end as '12月'
from test