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
Best Answer
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:
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.