Return table listing distinct values in each column

Question

I am familiarizing myself with a data table that has numerous columns for descriptions of individuals' job roles. I would like to return a table that lists in each column the distinct values present in the table for each column.

I understand how to return a list of distincts for a single column, but I would like all my results in one table, adjacent to each other. Order doesn't matter.

Exemplary input, where each row is one employee:

--title-- --function-- --class--
analyst    sleeping     professional
analyst    sleeping     ED
analyst    sleeping     MD
scientist  observing    VP
scientist  managing     VP
scientist  researching  associate

Expected output:

--title-- --function-- --class--
analyst   sleeping     professional
scientist observing    ED
          managing     MD
          researching  VP
                       associate

I'm confused by your input . The different job roles are _rows_ in that input, not _columns_, right ? — erik258, Dec 26 '18 at 18:12
That is something better done by client/display code, with the results of 3 separate queries. — Uueerdo, Dec 26 '18 at 18:13
Databases don't really do this, because the whole idea of a row is that everything on it is related. It can be done but in a low-end database like mysql it'll be fairly ugly unless you're on MySQL 8/MariaDB10. Are you absolutely sure it's what you want? Producing a 2 column output of "column_name, column_value" would be a lot more straight forward — Caius Jard, Dec 26 '18 at 18:21
Greenplum. @CaiusJard, thanks for your response. That's what I expected, but I wanted to see if there was some way to do this... — jmabs, Dec 26 '18 at 19:01
@jmabs sql databases _can_ do this, it just ends up very convoluted, over-complicated, and unnatural. It goes against their design principles to show unrelated data as though it were related. — Uueerdo, Dec 26 '18 at 19:07

score 1 · Answer 1 · answered Dec 26 '18 at 19:08

You might use this approach. However this kind of operation contradicts with relational stracture of the RDBMS systems.

select * from (
    select distinct title, null as function, null as class from Table1
    union
    select distinct null, function, null from Table1
    union 
    select distinct null, null, class from Table1
)
order by title, function, class

score 0 · Answer 2 · answered Dec 26 '18 at 20:05

You can do this using row_number() and aggregation:

select min(title) as title, min(function) as function, min(class) as class
from ((select title, null as function, null as class,
              row_number() over (order by title) as seqnum
       from t
       group by title
      ) union all
      (select null as title, function, null as class,
              row_number() over (order by function) as seqnum
       from t
       group by function
      ) union all
      (select null as title, null as function, class,
              row_number() over (order by class) as seqnum
       from t
       group by class
      )
     ) tfc
group by seqnum;

Return table listing distinct values in each column

2 Answers2