CREATE TABLE TEST01(VALUES1 VARCHAR2(10),VALUES2 VARCHAR2(10),VALUES3 VARCHAR2(10))
INSERT INTO TEST01 VALUES('1','2','3');
INSERT INTO TEST01 VALUES('1','2',' ');
INSERT INTO TEST01 VALUES('1',' ',' ');
INSERT INTO TEST01 VALUES(' ',' ','3');
INSERT INTO TEST01 VALUES(' ',' ',' ');
SELECT T1.VALUES1
,CASE WHEN T1.COUNT_DEPT=0 THEN '100%'
WHEN NVL(T1.VALUES1,' ')=' ' THEN '0'
ELSE TRUNC(1/T1.COUNT_DEPT,2)*100||'%' END RATIO1
,T1.VALUES2
,CASE WHEN T1.COUNT_DEPT=0 THEN '0'
WHEN NVL(T1.VALUES2,' ')=' ' THEN '0'
ELSE TRUNC(1/T1.COUNT_DEPT,2)*100||'%' END RATIO2
,T1.VALUES3
,CASE WHEN T1.COUNT_DEPT=0 THEN '0'
WHEN NVL(T1.VALUES3,' ')=' ' THEN '0'
ELSE TRUNC(1-TRUNC(((T1.COUNT_DEPT-1)/T1.COUNT_DEPT),2),2)*100||'%' END RATIO3
FROM
(
SELECT VALUES1,VALUES2,VALUES3,
CASE WHEN NVL(VALUES1,' ')=' ' THEN 0 ELSE 1 END+
CASE WHEN NVL(VALUES2,' ')=' ' THEN 0 ELSE 1 END+
CASE WHEN NVL(VALUES3,' ')=' ' THEN 0 ELSE 1 END AS COUNT_DEPT
FROM TEST01
)T1
INSERT INTO TEST01 VALUES('1','2','3');
INSERT INTO TEST01 VALUES('1','2',' ');
INSERT INTO TEST01 VALUES('1',' ',' ');
INSERT INTO TEST01 VALUES(' ',' ','3');
INSERT INTO TEST01 VALUES(' ',' ',' ');
SELECT T1.VALUES1
,CASE WHEN T1.COUNT_DEPT=0 THEN '100%'
WHEN NVL(T1.VALUES1,' ')=' ' THEN '0'
ELSE TRUNC(1/T1.COUNT_DEPT,2)*100||'%' END RATIO1
,T1.VALUES2
,CASE WHEN T1.COUNT_DEPT=0 THEN '0'
WHEN NVL(T1.VALUES2,' ')=' ' THEN '0'
ELSE TRUNC(1/T1.COUNT_DEPT,2)*100||'%' END RATIO2
,T1.VALUES3
,CASE WHEN T1.COUNT_DEPT=0 THEN '0'
WHEN NVL(T1.VALUES3,' ')=' ' THEN '0'
ELSE TRUNC(1-TRUNC(((T1.COUNT_DEPT-1)/T1.COUNT_DEPT),2),2)*100||'%' END RATIO3
FROM
(
SELECT VALUES1,VALUES2,VALUES3,
CASE WHEN NVL(VALUES1,' ')=' ' THEN 0 ELSE 1 END+
CASE WHEN NVL(VALUES2,' ')=' ' THEN 0 ELSE 1 END+
CASE WHEN NVL(VALUES3,' ')=' ' THEN 0 ELSE 1 END AS COUNT_DEPT
FROM TEST01
)T1