Home » SQL & PL/SQL » SQL & PL/SQL » Creating Pivot
Creating Pivot [message #269627] Sun, 23 September 2007 22:29 Go to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
I have a table which contain few columns (260). what I need is for each invividual column, I need the COUNT of how many records actualy have actual data and How many records are NULL

This should be displayed in a Pivot (Column as row) like below.

e.g. If the table A has 100 records

Count of Actual Data |Count of NULL
Column1 60 40

Column2 25 75

ColumnN 45 55

Any one have any idea how this can be done ?

[Updated on: Sun, 23 September 2007 23:53]

Report message to a moderator

Re: Creating Pivot [message #269655 is a reply to message #269627] Mon, 24 September 2007 00:36 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use analytical functions:
  1  with data as
  2  (select 'a'  as col1
  3   ,      2    as col2
  4   ,      null as col3
  5   ,      null as col4
  6   from   dual
  7   union all
  8   select 'b', null, sysdate, 'a'
  9   from    dual
 10   union all
 11   select 'a', null, null, 'a'
 12   from   dual
 13  )
 14  select count(col1) over ()
 15  ,      count(col2) over ()
 16  ,      count(col3) over ()
 17  ,      count(col4) over ()
 18* from   data
SQL> /

COUNT(COL1)OVER() COUNT(COL2)OVER() COUNT(COL3)OVER() COUNT(COL4)OVER()
----------------- ----------------- ----------------- -----------------
                3                 1                 1                 2
                3                 1                 1                 2
                3                 1                 1                 2

If I further tell you that count(*) over () will give you the total number of rows (in the resultset), then you should be able to calculate the number of NULL columns

[Updated on: Mon, 24 September 2007 00:38]

Report message to a moderator

Previous Topic: Exceptions no_data
Next Topic: Missing Comma
Goto Forum:
  


Current Time: Fri Dec 02 20:33:48 CST 2016

Total time taken to generate the page: 0.13260 seconds