--技术要点：行转列
--以下提供SQL SERVER语句
--创建测试环境
create table tab
(
machine int,
sernum varchar(10),
area varchar(2),
PF varchar(1)
)
--制造数据
insert into tab select 462,'16205R3E','AT','P'
insert into tab select 462,'16203H0N','AT','F'
insert into tab select 316,'1620A7WP','AT','S'
insert into tab select 316,'16206CCC','AT','S'
--1. 静态行转列(所谓静态，是指的PF列只有P,F,S这三个值，或者值是固定的某几个值)
select machine,
max(case pf when 'P' then num else 0 end) as P,
max(case pf when 'F' then num else 0 end) as F,
max(case pf when 'S' then num else 0 end) as S
from
(select machine,pf,count(1) as num from tab group by machine,pf
)tb
group by machine
/* 结果集
machine P F S
----------- ----------- ----------- -----------
316 0 0 2
462 1 1 0
(2 row(s) affected)
*/
--2. 动态行转列(相对于静态的概念)
declare @sql varchar(8000)
set @sql = 'select machine as ' + 'machine'
select @sql = @sql + ' , max(case pf when ''' + pf + ''' then num else 0 end) [' + pf + ']'
from (select distinct pf from tab) as a
set @sql = @sql + ' from (select machine,pf,count(1) as num from tab group by machine,pf
)tb group by machine'
exec(@sql)
/* 结果集
machine F P S
----------- ----------- ----------- -----------
316 0 0 2
462 1 1 0
(2 row(s) affected)
*/
--删除环境
drop table tab
select matchine
,count(case when pf = 'S' then 1 else 0 end) as 'S'
,count(case when pf = 'P then 1 else 0 end) as 'P'
,count(case when pf = 'F' then 1 else 0 end) as 'F'
from table
GROUP BY matchine
select machine,
sum(decode(PF,'S',1,0)) S,
sum(decode(PF,'P',1,0)) P,
sum(decode(PF,'F',1,0)) F
from tab
group by machine
内容全部来源于网络收集，如有侵权，请联系网站删除：QQ：24596024