In the last issue of Oracle Magazine, Tom Kyte shows cool solutions to some questions he got on asktom.oracle.com, using pure SQL. One of the nice things in SQL is that one problem may have many different solutions (I wrote about it once here).
One of the questions there is about counting distinct values across several columns within each row. You can see the question, Tom’s answer and several alternative answers suggested by other people here. I added a suggested alternative of my own, using two SQL functions that are not very known, so I thought it’s worth mentioning them here.
These two functions are SET and CARDINALITY, and both get a nested table as their input parameter.
SET returns a nested table of the same type as the input parameter, after removing duplicate elements from it.
CARDINALITY returns the number of elements in a nested table.
Both functions exist since Oracle 10g Release 1.
Let’s create a nested table type:
create type string_ntt as table of varchar2(4000) /
and construct an object of this type, using the default constructor, containing 6 elements, out of which 4 are unique:
select string_ntt('A','B','C','B','D','A') as my_list from dual;
MY_LIST
------------------------------------------
STRING_NTT('A', 'B', 'C', 'B', 'D', 'A')
If we apply the CARDINALITY function on this object, we will get the number of elements it contains:
select CARDINALITY(string_ntt('A','B','C','B','D','A')) as num_of_elements from dual;
NUM_OF_ELEMENTS
---------------
6
If we apply the SET function on this object, we will get a new object of the same type, with only the 4 unique values:
select SET(string_ntt('A','B','C','B','D','A')) as my_set from dual;
MY_SET
----------------------------------------
STRING_NTT('A', 'B', 'C', 'D')
And by combining both CARDINALITY and SET, we will get the number of unique elements in the nested table object:
select CARDINALITY(SET(string_ntt('A','B','C','B','D','A'))) as num_of_unique_elements from dual;
NUM_OF_UNIQUE_ELEMENTS
----------------------
4