| Creating Pivot [message #269627] |
Sun, 23 September 2007 22:29  |
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  |
Frank
Messages: 7901
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
