合并多行记录问题!

Question

try:
select
    编号,max(设备1),max(num1),max(设备2),max(num2),max(设备3),max(num3),max(设备4),max(num4),max(设备5),max(num5)
from
    表
group by
    编号

Answer 1

select 编号
        ,max(设备1)as 设备1,max(num) as num
       ,max(设备2)as 设备2,max(num2)as num2
       ,max(设备3)as 设备3,max(num3)as num3
       ,max(设备4)as 设备4,max(num4)as num4
       ,max(设备5)as 设备5,max(num5)as num5
from 表
group by 编号

Answer 2

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
    编号,
    设备1=max(设备1),
    num1 =max(num1),
    设备2=max(设备2),
    num2 =max(num2),
    设备3=max(设备3),
    num3 =max(num3),
    设备4=max(设备4),
    num4 =max(num4),
    设备5=max(设备5),
    num5 =max(num5)
from
    @t
group by
    编号/*
编号       设备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
*/

Answer 3

create table tb(编号 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 tb
select     '230110001','',0,'电冰箱',9,'',0,'',0,'',0 union all
select     '230110001','',0,'',      0,'',0,'',0,'计算机',9 union all
select     '230110002','',0,'电冰箱',9,'',0,'',0,'',0select 编号,
[设备1]=max(设备1),num1=max(num1),
[设备2]=max(设备2),num2=max(num2),
[设备3]=max(设备3),num3=max(num3),
[设备4]=max(设备4),num4=max(num4),
[设备5]=max(设备5),num5=max(num5)
 from tb group by 编号
/*
编号 设备1 num1 设备2 num2 设备3 num3 设备4 num4 设备5 num5
230110001 0 电冰箱 9 0 0 计算机 9
230110002 0 电冰箱 9 0 0 0
*/drop table tb

Answer 4

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 @Tselect
    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 #

Answer 5

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 @Tselect
    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 #