--前提.opttypename是固定的
select EquClassname,equtypename ,
sum(case when opttypename = '返修入库' else optnumsum else 0 end ) 返修入库,
sum(case when opttypename = '待修出库' else optnumsum else 0 end ) 待修出库,
sum(case when opttypename = '报废' else optnumsum else 0 end ) 报废,
sum(case when opttypename = '修完入库' else optnumsum else 0 end ) 修完入库,
sum(case when opttypename = '发货出库' else optnumsum else 0 end ) 发货出库
from 表
group by EquClassname,equtypename
select EquClassname,equtypename ,
sum(case when opttypename = '返修入库' else optnumsum else 0 end ) 返修入库,
sum(case when opttypename = '待修出库' else optnumsum else 0 end ) 待修出库,
sum(case when opttypename = '报废' else optnumsum else 0 end ) 报废,
sum(case when opttypename = '修完入库' else optnumsum else 0 end ) 修完入库,
sum(case when opttypename = '发货出库' else optnumsum else 0 end ) 发货出库
from 表
group by EquClassname,equtypename
EquClassname,
equtypename,
返修入库 = Max(Case opttypename When '返修入库' Then optnumsum Else 0 End),
待修出库 = Max(Case opttypename When '待修出库' Then optnumsum Else 0 End),
报废 = Max(Case opttypename When '报废' Then optnumsum Else 0 End),
修完入库 = Max(Case opttypename When '修完入库' Then optnumsum Else 0 End),
发货出库 = Max(Case opttypename When '发货出库' Then optnumsum Else 0 End)
From
表
Group By
EquClassname,
equtypename
insert into tb values('监控单元',2,'HRWCSU',12,'返修入库',37)
insert into tb values('监控单元',2,'HRWCSU',12,'待修出库',16)
insert into tb values('监控单元',2,'HRWCSU',12,'报废',4)
insert into tb values('监控单元',2,'HRWCSU',12,'修完入库',10)
insert into tb values('监控单元',2,'HRWCSU',12,'发货出库',10)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'返修入库',35)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'待修出库',20)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'报废',2)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'修完入库',15)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'发货出库',10)
select EquClassname,equtypename ,
sum(case when opttypename = '返修入库' then optnumsum else 0 end ) 返修入库,
sum(case when opttypename = '待修出库' then optnumsum else 0 end ) 待修出库,
sum(case when opttypename = '报废' then optnumsum else 0 end ) 报废,
sum(case when opttypename = '修完入库' then optnumsum else 0 end ) 修完入库,
sum(case when opttypename = '发货出库' then optnumsum else 0 end ) 发货出库
from tb
group by EquClassname,equtypenamedrop table tb
/*
EquClassname equtypename 返修入库 待修出库 报废 修完入库 发货出库
------------ -------------------- ----------- ----------- ----------- ----------- -----------
电源模块 HRM1S50 48V/50A 35 20 2 15 10
监控单元 HRWCSU 37 16 4 10 10(所影响的行数为 2 行)
*/
insert into tb values('监控单元',2,'HRWCSU',12,'返修入库',37)
insert into tb values('监控单元',2,'HRWCSU',12,'待修出库',16)
insert into tb values('监控单元',2,'HRWCSU',12,'报废',4)
insert into tb values('监控单元',2,'HRWCSU',12,'修完入库',10)
insert into tb values('监控单元',2,'HRWCSU',12,'发货出库',10)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'返修入库',35)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'待修出库',20)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'报废',2)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'修完入库',15)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'发货出库',10)
declare @sql varchar(8000)
set @sql = 'select EquClassname,equtypename'
select @sql = @sql + ' , sum(case opttypename when ''' + opttypename + ''' then optnumsum else 0 end) [' + opttypename + ']'
from (select distinct opttypename from tb) as a
set @sql = @sql + ' from tb group by EquClassname,equtypename'
exec(@sql) drop table tb/*
EquClassname equtypename 报废 待修出库 发货出库 返修入库 修完入库
------------ -------------------- ----------- ----------- ----------- ----------- -----------
电源模块 HRM1S50 48V/50A 2 20 10 35 15
监控单元 HRWCSU 4 16 10 37 10
*/
Select @S = 'Select EquClassname, equtypename'
Select @S = @S + ', ' + opttypename + ' = Max(Case opttypename When ''' + opttypename + ''' Then optnumsum Else 0 End)'
From 表 Group By opttypename
Select @S = @S + ' From 表 Group By EquClassname, equtypename'
EXEC(@S)
insert into tb values('监控单元',2,'HRWCSU',12,'返修入库',37)
insert into tb values('监控单元',2,'HRWCSU',12,'待修出库',16)
insert into tb values('监控单元',2,'HRWCSU',12,'报废',4)
insert into tb values('监控单元',2,'HRWCSU',12,'修完入库',10)
insert into tb values('监控单元',2,'HRWCSU',12,'发货出库',10)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'返修入库',35)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'待修出库',20)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'报废',2)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'修完入库',15)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'发货出库',10)
select EquClassname,equtypename ,
sum(case when opttypename = '返修入库' then optnumsum else 0 end ) 返修入库,
sum(case when opttypename = '待修出库' then optnumsum else 0 end ) 待修出库,
sum(case when opttypename = '报废' then optnumsum else 0 end ) 报废,
sum(case when opttypename = '修完入库' then optnumsum else 0 end ) 修完入库,
sum(case when opttypename = '发货出库' then optnumsum else 0 end ) 发货出库
from tb
group by EquClassname,equtypenamedrop table tb/*
EquClassname equtypename 返修入库 待修出库 报废 修完入库 发货出库
------------ --------------- ------ -------- ---- ------- -----------
电源模块 HRM1S50 48V/50A 35 20 2 15 10
监控单元 HRWCSU 37 16 4 10 10(所影响的行数为 2 行)
*/
insert into tb values('监控单元',2,'HRWCSU',12,'返修入库',37)
insert into tb values('监控单元',2,'HRWCSU',12,'待修出库',16)
insert into tb values('监控单元',2,'HRWCSU',12,'报废',4)
insert into tb values('监控单元',2,'HRWCSU',12,'修完入库',10)
insert into tb values('监控单元',2,'HRWCSU',12,'发货出库',10)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'返修入库',35)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'待修出库',20)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'报废',2)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'修完入库',15)
insert into tb values('电源模块',3,'HRM1S50 48V/50A',1,'发货出库',10)
go
declare @sql varchar(8000)
set @sql = 'select EquClassname,equtypename'
select @sql = @sql + ' , sum(case opttypename when ''' + opttypename + ''' then optnumsum else 0 end) [' + opttypename + ']'
from (select distinct opttypename from tb) as a
set @sql = @sql + ' from tb group by EquClassname,equtypename'
exec(@sql) drop table tb/*
EquClassname equtypename 报废 待修出库 发货出库 返修入库 修完入库
------------ --------------- --- ------- ------- -------- -----------
电源模块 HRM1S50 48V/50A 2 20 10 35 15
监控单元 HRWCSU 4 16 10 37 10
*/