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