ORACLE 交叉报表固定列实现方法

ORACLE 交叉报表固定列实现方法

需求：
统计某些天内每个类型的数量，

原数据 MCTYPE 类型：DTH，MED，OTH
TDay时间划分：T1,T2,T3,T4~T10
原始data
如果DTHIND=1 那么这条记录类型就是DTH类型，其他类推

id	DTHIND	MEDIND	OTHIND
16006723	0	1	0
16006730	0	1	0
16006735	0	1	0
16006744	1	0	0
16006757	1	0	0
16006772	1	0	0
16007009	0	1	0

使用交叉报表后show 出的data

MCTYPE	T1	T2	T3	T4~T10
DTH	1	0	0	2
MED	0	0	1	3

但是Clients 需求是不管是否有data，都要show 出全部类型，全部日期
期望结果是

MCTYPE	T1	T2	T3	T4~T10
DTH	1	0	0	2
MED	0	0	1	3
OTH	0	0	0	0

原代码贴上代码

select MCTYPE,
  COUNT(DECODE(TDATE,'T1','T1',NULL)) AS T1,
  COUNT(DECODE(TDATE,'T2','T2',NULL)) AS T2,
  COUNT(DECODE(TDATE,'T3','T3',NULL)) AS T3,
  COUNT(DECODE(TDATE,'T4~T10','T4',NULL)) AS "T4~T10" FROM (
select case when sc.dthind=1 then 'DTH'
    when sc.medind=1 then 'MED'
    when sc.othind=1 then 'OTH' else null end  as mctype,
    case when sc.crtdate >=(SELECT BIZDATE FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE+1 FROM SYSINFO) then 'T0'
    when sc.crtdate >=(SELECT BIZDATE-1 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE FROM SYSINFO) then 'T1'
    when sc.crtdate >=(SELECT BIZDATE-2 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE-1 FROM SYSINFO) then 'T2'
    when sc.crtdate >=(SELECT BIZDATE-3 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE-2 FROM SYSINFO) then 'T3'
    when sc.crtdate >=(SELECT BIZDATE-10 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE-3 FROM SYSINFO) then 'T4~T10'
    else null end as Tdate
    from cccbsc sc where sc.clmnum in ('16006772','16007009','16006744','16006757','16006723','16006730','16006735')
  and  crtdate >=(SELECT BIZDATE - 10 FROM SYSINFO) 
      AND crtdate < (SELECT BIZDATE FROM SYSINFO)) GROUP BY MCTYPE 

实现方法是
union all 所有的行和列一遍，这里是3*4，也就是union all 12条记录，然后所有统计的数量
减一条

select MCTYPE,
  COUNT(DECODE(TDATE,'T1','T1',NULL))-1 AS T1,
  COUNT(DECODE(TDATE,'T2','T2',NULL))-1 AS T2,
  COUNT(DECODE(TDATE,'T3','T3',NULL))-1 AS T3,
  COUNT(DECODE(TDATE,'T4~T10','T4',NULL))-1 AS "T4~T10" FROM (
select case when sc.dthind=1 then 'DTH'
    when sc.medind=1 then 'MED'
    when sc.othind=1 then 'OTH' else null end  as mctype,
    case when sc.crtdate >=(SELECT BIZDATE FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE+1 FROM SYSINFO) then 'T0'
    when sc.crtdate >=(SELECT BIZDATE-1 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE FROM SYSINFO) then 'T1'
    when sc.crtdate >=(SELECT BIZDATE-2 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE-1 FROM SYSINFO) then 'T2'
    when sc.crtdate >=(SELECT BIZDATE-3 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE-2 FROM SYSINFO) then 'T3'
    when sc.crtdate >=(SELECT BIZDATE-10 FROM SYSINFO) and sc.crtdate<=(SELECT BIZDATE-3 FROM SYSINFO) then 'T4~T10'
    else null end as Tdate
    from cccbsc sc where sc.clmnum in ('16006772','16007009','16006744','16006757','16006723','16006730','16006735')
  and  crtdate >=(SELECT BIZDATE - 10 FROM SYSINFO) 
      AND crtdate < (SELECT BIZDATE FROM SYSINFO)
      union all select 'DTH' AS MCTYPE,'T1' AS TDATE FROM DUAL 
union all select 'DTH' AS MCTYPE,'T2' AS TDATE FROM DUAL
union all select 'DTH' AS MCTYPE,'T3' AS TDATE FROM DUAL 
union all select 'DTH' AS MCTYPE,'T4~T10' AS TDATE FROM DUAL
union all select 'MED' AS MCTYPE,'T1' AS TDATE FROM DUAL 
union all select 'MED' AS MCTYPE,'T2' AS TDATE FROM DUAL
union all select 'MED' AS MCTYPE,'T3' AS TDATE FROM DUAL 
union all select 'MED' AS MCTYPE,'T4~T10' AS TDATE FROM DUAL
union all select 'OTH' AS MCTYPE,'T1' AS TDATE FROM DUAL 
union all select 'OTH' AS MCTYPE,'T2' AS TDATE FROM DUAL
union all select 'OTH' AS MCTYPE,'T3' AS TDATE FROM DUAL 
union all select 'OTH' AS MCTYPE,'T4~T10' AS TDATE FROM DUAL) GROUP BY MCTYPE 
;

原理其实就是group by 的时候让每个行和列都有值，然后show的时候再把加多的减去就可以了。
如果有什么其他简单的方法可以固定行和列也可以告诉我，谢谢