I have a table that looks like this. And I want to get the distinct count across the three columns.
| ID | Column1 | Column 2 | Column 3 |
|---|---|---|---|
| 1 | A | B | C |
| 2 | A | A | B |
| 3 | A | A |
The desired output I'm looking for is:
| ID | Column1 | Column 2 | Column 3 | unique_count |
|---|---|---|---|---|
| 1 | A | B | C | 3 |
| 2 | A | A | B | 2 |
| 3 | A | A | 1 |
CodePudding user response：
In standard SQL, you have to UNPIVOT first, do the count(distinct) group by on the PIVOTed result and then PIVOT again.
In recent ORACLE version, you could write your own Polymorphic Table Function to do it, passing the table and the list of columns to count for DISTINCT values.
CodePudding user response：
try if this works
SELECT Count(*)
FROM (SELECT DISTINCT Column1 FROM TABLENAME);