如一個表

A(maintains_id part_name model_id notes

1 AB 0001 23

1 AC 0001 23

1 AB 0002 23

2 AB 0001 14

2 AC 0002 14

2 AD 033 14)

變成

A(maintains_id part_name model_id notes

1 AB,AC 0001,0002 23

2 AB,AC,AD 0001,0002,033 14)

就是maintains_id字段的數據不可重復，但是其後面對應的part_name,model_id兩字段如果是相等就不相加，不相等要將相應的字段相加起來，請問各位高手如何用SQL語句實現，最好用游標的方式

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

表名為mc_scheduler_in
in_detail_id scheduler_code scheduler_month pmat_id aty_need
68 Scheduler001 1 Y005000033 6.0000
69 Scheduler001 1 SF20512 .0000
70 Scheduler001 1 P005000010 .0000
71 Scheduler001 2 P012000035 22.5000
72 Scheduler001 2 Y012000858 22.5000
73 Scheduler001 3 SF21936 .0000
74 Scheduler001 3 P001000016 23.5200
75 Scheduler001 3 Y001000015 10.0800
處理成如下形式
in_detail_id scheduler_code pmat_id 1月 2月 3月 4月
68 Scheduler001 Y005000033 6.0000
69 Scheduler001 SF20512 .0000
70 Scheduler001 P005000010 .0000
71 Scheduler001 P012000035 2.5000
72 Scheduler001 Y012000858 22.5000
73 Scheduler001 SF21936 .0000
74 Scheduler001 P001000016 23.5200
75 Scheduler001 Y001000015 10.0800
就是是scheduler_month相應月份對應的qty_need只會出現在相應月份的下面，其它的位置不變，請問各位高手如何實現,我今天機子有問題發不了新貼，只好放在這

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编辑过]

前面重復問題我已解決，現在就是第三樓的這個問題，我不希望 用12個臨時表來解決，這樣太浪費了。

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

非常感謝棉花糖，向你學習。