列转行,然后再汇总,11g以上版本可以考虑使用unpivot
select value,count(1) from(
select a as value from table1
union all
select b as value from table1
……
)group by value
select value,count(1) from(
select a as value from table1
union all
select b as value from table1
……
)group by value
CREATE OR REPLACE PROCEDURE P_RESULT(P_TABLE IN VARCHAR2,
X_RESULT OUT SYS_REFCURSOR) IS
V_SQL CLOB;
CURSOR CUR_COL IS
SELECT COLUMN_NAME FROM USER_COL_COMMENTS WHERE TABLE_NAME = UPPER(P_TABLE);
REC_COL CUR_COL%ROWTYPE;
BEGIN
OPEN CUR_COL;
LOOP
FETCH CUR_COL
INTO REC_COL;
V_SQL := V_SQL || 'SELECT ' || REC_COL.COLUMN_NAME ||
' AS F_VALUE FROM ' || UPPER(P_TABLE);
EXIT WHEN CUR_COL%NOTFOUND;
IF (CUR_COL%FOUND) THEN
V_SQL := V_SQL || ' UNION ALL ';
END IF;
END LOOP;
CLOSE CUR_COL;
V_SQL := 'SELECT F_VALUE,COUNT(1) FROM ( ' || V_SQL ||
' ) GROUP BY F_VALUE';
OPEN X_RESULT FOR V_SQL;
END P_RESULT;
也可以试试润乾的集算器(免费版即可),能够方便地支持动态列,脚本大概是这样: 其中,A2将所有字段值纵向拼接成一列,然后在A3中进行分组统计出每个分组的成员个数,通过分步的代码逐步实现,每步的结果都能在编辑器里实时查看。
A4结果: 集算器提供JDBC接口,可以象数据库一样嵌入到应用程序中,用起来很简单。