Sql 中的转置

 

--列转行

 

create or replace view qua.view_plat_trans1 as

with t1 as

(select distinct

       pdate,

       mat_nm,

       mat_cd,

       shift_nm,

       team_nm,

       plat_nm,

       checker,

       check_time,

       para_nm,

       reject_nm,

       para_max,

       para_min,

       para_ran,--极差

       para_avg,--均值

       para_std,--标偏

       para_by,--变异系数

       para_cpk,

       time_stamp

  from qua.spc_check_plat_data)

  --烟支吸阻  均值

  select t1.pdate,t1.mat_nm,t1.mat_cd,t1.shift_nm,t1.team_nm,t1.plat_nm,t1.checker,t1.check_time,t1.para_nm,'4031' as para_ID,'烟支 吸阻均值' as stc_nm,para_avg as parameter_value  from t1

  where t1.para_nm='烟支 吸阻(Pa)'

  union all

    select t1.pdate,t1.mat_nm,t1.mat_cd,t1.shift_nm,t1.team_nm,t1.plat_nm,t1.checker,t1.check_time,t1.para_nm,'3940' as para_ID,'烟支 吸阻-最大值' as stc_nm,para_max as parameter_value  from t1

  where t1.para_nm='烟支 吸阻(Pa)'

   union all

    select t1.pdate,t1.mat_nm,t1.mat_cd,t1.shift_nm,t1.team_nm,t1.plat_nm,t1.checker,t1.check_time,t1.para_nm,'3941' as para_ID,'烟支 吸阻-最小值' as stc_nm,para_min as parameter_value  from t1

  where t1.para_nm='烟支 吸阻(Pa)'

   union all

    select t1.pdate,t1.mat_nm,t1.mat_cd,t1.shift_nm,t1.team_nm,t1.plat_nm,t1.checker,t1.check_time,t1.para_nm,'3943' as para_ID,'烟支 吸阻-标准差' as stc_nm,para_std as parameter_value  from t1

  where t1.para_nm='烟支 吸阻(Pa)'

  union all

    select t1.pdate,t1.mat_nm,t1.mat_cd,t1.shift_nm,t1.team_nm,t1.plat_nm,t1.checker,t1.check_time,t1.para_nm,'3945' as para_ID,'烟支 吸阻-不合格支数' as stc_nm,reject_nm as parameter_value  from t1

  where t1.para_nm='烟支 吸阻(Pa)'

 

 

--行转列

CREATE OR REPLACE VIEW QUA.VIEW_CUT_CHECK_RECORD_PQM AS
SELECT
vccrp.REQUEST_ID,vccrp.WO_ID,vccrp.LOT_ID,vccrp.MAT_ID,vccrp.ROUTING_ID,vccrp.TECH_ID,vccrp.SAMPLE_CENT,vccrp.PARA_ID,vccrp.CHECK_VALUE,vccrp.SCORE,vccrp.START_TIME,vccrp.PROC_ID,vccrp.SET_VALUE,vccrp.CONTROL_UP_VALUE,vccrp.ACCEPT_UP_VALUE,vccrp.ACCEPT_LOW_VALUE,vccrp.CONTROL_LOW_VALUE,
MAX(CASE WHEN vccrp2.PARA_ID=CAST(ps.PARA_AVG AS VARCHAR(50)) THEN vccrp2.CHECK_VALUE ELSE NULL END) AS CHECK_AVG,
MAX(CASE WHEN vccrp2.PARA_ID=CAST(ps.PARA_MAX AS VARCHAR(50)) THEN vccrp2.CHECK_VALUE ELSE NULL END) AS CHECK_MAX,
MAX(CASE WHEN vccrp2.PARA_ID=CAST(ps.PARA_MIN AS VARCHAR(50)) THEN vccrp2.CHECK_VALUE ELSE NULL END) AS CHECK_MIN,
MAX(CASE WHEN vccrp2.PARA_ID=CAST(ps.PARA_SDEV AS VARCHAR(50)) THEN vccrp2.CHECK_VALUE ELSE NULL END) AS CHECK_SD,vccrp.sample_id
FROM qua.VIEW_CUT_CHECK_RECORD_PQM_new  vccrp,qua.VIEW_CUT_CHECK_RECORD_PQM_new vccrp2,spc.PARA_STAT ps
WHERE vccrp.PARA_ID=ps.PARA_ID
AND vccrp.REQUEST_ID=vccrp2.REQUEST_ID
)
GROUP BY vccrp.REQUEST_ID,vccrp.WO_ID,vccrp.LOT_ID,vccrp.MAT_ID,vccrp.ROUTING_ID,vccrp.TECH_ID,vccrp.SAMPLE_CENT,vccrp.PARA_ID,vccrp.CHECK_VALUE,vccrp.SCORE,
vccrp.START_TIME,vccrp.PROC_ID,vccrp.SET_VALUE,vccrp.CONTROL_UP_VALUE,vccrp.ACCEPT_UP_VALUE,vccrp.ACCEPT_LOW_VALUE,vccrp.CONTROL_LOW_VALUE,vccrp.sample_id;