CODEWITHSUNDEEP
sqlsql-serveroracle-database
Andrew
Andrew

Reputation: 31

Getting a count by value of all columns in a table using Oracle SQL

For various reasons the organisation I work for has data stored on both Oracle and MS SQL server databases. We are moving some static historical data over and I have to check that the data has been moved properly. The Query below checks the data in SQL server and produces a table listing counts of all the values in each column of the table. Due to formatting differences in Oracle I will need to group by two other columns Year and Iteration_count . I have not been able to get a loop through of all columns in a table working in Oracle as my experience is pretty much limited to SQL Server

DECLARE @SQL NVARCHAR(MAX) = ''
SELECT @SQL = STUFF((SELECT ' UNION SELECT ''' + name 
                            + ''' AS [Column], ' 
                            + 'CAST(' + QUOTENAME(Name)
                            + ' AS NVARCHAR(MAX)) AS [ColumnValue], COUNT(*) AS [Count] FROM ' 
                            +'dbo.HES_APC_ACP_9798' 
                            +' where (NUMACP IS NOT NULL AND NOT (NUMACP = 0) ) '
                            +' GROUP BY ' + QUOTENAME(Name)
                            --+'Order By [Column],[ColumnValue]'
                    FROM   sys.columns 
                    WHERE  object_id = Object_id('dbo.HES_APC_ACP_9798' )

                    FOR XML PATH ('')), 1, 7, '');

EXECUTE sp_executesql @SQL;

Upvotes: 3

Views: 1713

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

This loop on user_tab_columns should help:

declare
  v_table varchar2(30) := 'TEST';
  v_sql varchar2(32767);
begin
  for r in (select column_name name from user_tab_cols 
              where table_name=v_table order by column_id)
  loop
    v_sql := v_sql||' union all select '''||r.name||''' col_name, to_char('
                  ||r.name||') col_value, count(1) cnt from '||v_table
                  ||' group by '||r.name||chr(13);
  end loop;
  v_sql := ltrim(v_sql, ' union all ');
  dbms_output.put_line(v_sql);
end;

Test table:

create table test (col1 varchar2(10), col2 number(5), col3 date);
insert into test values ('ABC', 1, null);
insert into test values ('DEF', 1, date '2015-06-18');

Executing first PLSQL block outputs:

select 'COL1' col_name, to_char(COL1) col_value, count(1) cnt from TEST group by COL1
 union all select 'COL2' col_name, to_char(COL2) col_value, count(1) cnt from TEST group by COL2
 union all select 'COL3' col_name, to_char(COL3) col_value, count(1) cnt from TEST group by COL3

Output of this query:

COL_NAME COL_VALUE           CNT
-------- ------------ ----------
COL1     DEF                   1
COL1     ABC                   1
COL2     1                     2
COL3                           1
COL3     15/06/18              1

Use all_tab_cols and add filter for owner if you read data from other schema. You can also run generated query using execute immediate statement.

Upvotes: 1

Related Questions