How to return all the unique column values on a per column basis for all metadata tables?

Question

assume multiple tables of various sizes

table one

col one, col two, col three,...
a,d,j
a,e,k
a,e,m
b,e,o
c,e,o
d,e,o
f,e,o

return (preferably in this format)

col one, col two, col three
a,d,j
b,e,k
c, ,m
d, ,o
f, ,

the idea is to be able to give a summary of the data to the users to check data quality prior to a migration

Answer 1

I'd use DENSE_RANK() to match values from distinct rows.

And MAX() OVER() to detemine which column has the biggest number of values (only for the ORDER BY purposes, you can skip this if the order doesn't matter).

Here is a working example:

create table test2 (
colone char(1), coltwo char(1), colthree char(1));

insert into test2 values
('a','d','j'),
('a','e','k'),
('a','e','m'),
('b','e','o'),
('c','e','o'),
('d','e','o'),
('f','e','o')
;

WITH cte AS(
    SELECT 
        colone, DENSE_RANK () OVER (ORDER BY colone) as c1rn, 
        coltwo, DENSE_RANK () OVER (ORDER BY coltwo) as c2rn, 
        colthree, DENSE_RANK () OVER (ORDER BY colthree) as c3rn
    FROM test2
) , cte2 as (
    SELECT 
        t1.colone, t2.coltwo, t3.colthree,
        MAX(t1.c1rn) OVER() as max_c1rn,
        MAX(t2.c2rn) OVER() as max_c2rn,
        MAX(t3.c3rn) OVER() as max_c3rn
    FROM cte  t1
    FULL OUTER JOIN 
    cte t2 ON t1.c1rn = t2.c2rn
    FULL OUTER JOIN 
    cte t3 ON 
    t1.c1rn = t3.c3rn
    OR 
    t2.c2rn = t3.c3rn
)
SELECT  
colone, coltwo, colthree 
FROM cte2
GROUP BY colone, coltwo, colthree
ORDER BY 
    CASE WHEN colone IS NULL THEN 1 ELSE 0 END, colone,
    CASE WHEN coltwo IS NULL THEN 1 ELSE 0 END, coltwo,
    CASE WHEN colthree IS NULL THEN 1 ELSE 0 END, colthree
;

Hopefully your table is not very big, it will probably be scanned as many times as many columns you have, so it may take quite a lot of time.

Answer 2

It's possible, but not really efficient.

You can get distinct values plus a ROW_NUMBER:

select col1, row_number() over (order by col1) as rn
from tab 
group by col1

If you start with the biggest result it's simply LEFT JOINing to all others, if you don't know which result has the maximum number of rows it's getting really ugly with ... FULL JOIN tx ON tx.rn = (COALESCE(t1.rn, t2.rn, t3.rn, ... tx-1.rn)

So you might better change the desired format...