子陌红尘《《感谢钻钻的答案！不过现在还是不对滴！有钻的帖子要加分滴！

[qiuming0306]

http://topic.csdn.net/u/20080409/11/9362a3af-ba30-4099-a275-5fa47a4c6352.html
再加50分一起结贴子！

[-狙击手-]

SF

[子陌红尘]

declare @T table(编号 varchar(10),设备1 varchar(10),num1 int, 
                                  设备2 varchar(10),num2 int, 
                                  设备3 varchar(10),num3 int, 
                                  设备4 varchar(10),num4 int,  
                                  设备5 varchar(10),num5 int) 

insert into @T values('230110001','电冰箱',9,'0',0,'0'     ,0,'0',0,'0',0) 
insert into @T values('230110001','0'     ,0,'0',0,'计算机',9,'0',0,'0',0) 
insert into @T values('230110002','电冰箱',9,'0',0,'0'     ,0,'0',0,'0',0) 

select identity(int,1,1) as id,* into # from @T 

select 
    c.编号, 
    设备1=max(c.设备1), 
    num1 =max(c.num1), 
    设备2=max(c.设备2), 
    num2 =max(c.num2), 
    设备3=max(c.设备3), 
    num3 =max(c.num3), 
    设备4=max(c.设备4), 
    num4 =max(c.num4), 
    设备5=max(c.设备5), 
    num5 =max(c.num5) 

from 
    (select 
         a.编号,a.设备1,a.num1,a.设备2,a.num2,a.设备3,a.num3,a.设备4,a.num4,a.设备5,a.num5,count(1) as rowid 
     from 
         (select t.*, 
                 (case when t.设备1!='0' then 1  
                       when t.设备2!='0' then 2  
                       when t.设备3!='0' then 3  
                       when t.设备4!='0' then 4  
                       when t.设备5!='0' then 5  
                  end) as code 
          from # t) a, 
          (select t.*, 
                 (case when t.设备1!='0' then 1  
                       when t.设备2!='0' then 2  
                       when t.设备3!='0' then 3  
                       when t.设备4!='0' then 4  
                       when t.设备5!='0' then 5  
                  end) as code 
          from # t) b 
     where 
         a.编号=b.编号 and a.code=b.code and a.id>=b.id 
     group by 
         a.编号,a.设备1,a.num1,a.设备2,a.num2,a.设备3,a.num3,a.设备4,a.num4,a.设备5,a.num5) c 
group by 
    c.编号,c.rowid 

/* 
编号         设备1        num1        设备2        num2        设备3        num3        设备4        num4        设备5        num5         
---------- ---------- ----------- ---------- ----------- ---------- ----------- ---------- ----------- ---------- -----------  
230110001  电冰箱        9           0          0           计算机        9           0          0           0          0 
230110002  电冰箱        9           0          0           0          0           0          0           0          0 
*/ 

drop table #

[-狙击手-]

insert into @T values('230110001','电冰箱',9,'0',0,'0' ,0,'0',0,'0',0)
insert into @T values('230110001','电冰箱',19,'0',0,'0' ,0,'0',0,'0',0)

-------------------------------

这样的记录要合并吗？

[utpcb]

哈哈做钻真好！　　没有人给我分数阿哈哈

[qiuming0306]

[Quote=引用 3 楼 happyflystone 的回复:]
insert into @T values('230110001','电冰箱',9,'0',0,'0' ,0,'0',0,'0',0)
insert into @T values('230110001','电冰箱',19,'0',0,'0' ,0,'0',0,'0',0)

-------------------------------

这样的记录要合并吗？
[/Quote]
有的话要加在一起，不过应当没有这样的数据的！