Hi,
I have a wide table with 200 odd columns. Requirement is to pivot the columns and display the unique values and count of blanks within each column
CREATE TABLE example(
c1 VARCHAR(10),
c2 VARCHAR(10),
c3 VARCHAR(10)
);
/
INSERT INTO example VALUES('abc', '123', 'speed');
INSERT INTO example VALUES('cat', 'crazy', 'no');
INSERT INTO example VALUES('dog', 'yes', 'no');
INSERT INTO example VALUES('dog', 'row', 'no');
INSERT INTO example VALUES(null,null,'no');
commit;
/Result should be in the pivoted format as given below.
unique Countofblank
-----------------------
c1 4 1
c2 5 1
c3 2 0 Could you please help ?
Thanks
Rather than PIVOT, it sounds like you want to:
- UNPIVOT the columns
- Group by these to get the counts
Note count ( distinct ) excludes null values. From your output it looks like you want to include these in your distinct count. So you need to map NULL to some "impossible" value (e.g. the string "NULL").
To get the nulls you also need to map these to a non-null value. And the non-nulls to null :)
Which gives:
CREATE TABLE example(
c1 VARCHAR(10),
c2 VARCHAR(10),
c3 VARCHAR(10)
);
/
INSERT INTO example VALUES('abc', '123', 'speed');
INSERT INTO example VALUES('cat', 'crazy', 'no');
INSERT INTO example VALUES('dog', 'yes', 'no');
INSERT INTO example VALUES('dog', 'row', 'no');
INSERT INTO example VALUES(null,null,'no');
commit;
/
with rws as (
select * from example
unpivot include nulls (
val for col in (
c1, c2, c3
)
)
)
select col,
count ( distinct val ) distinct_vals,
count ( distinct case when val is null then 'NULL' else val end ) distinct_vals_with_null,
count ( case when val is null then 1 end ) nulls
from rws
group by col;
CO DISTINCT_VALS DISTINCT_VALS_WITH_NULL NULLS
-- ------------- ----------------------- ----------
C1 3 4 1
C2 4 5 1
C3 2 2 0