1

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

5
  • What's the logic behind? What does this summary represent? Commented Mar 3, 2016 at 8:40
  • It represents a distinct set of all the possible metadata values. Commented Mar 3, 2016 at 9:00
  • It is not meaningful to have the distinct values presented in a single table as though (b,e,k) is a tuple from the domain. Doing so implies some semantics between "b", "e" and "k", which is not so. Produce each list separately. Use a bit of reporting software which allows the lists to be top-aligned if you really need to. Commented Mar 3, 2016 at 11:45
  • I guess if your users are stupid, correct. Luckily mine are able to read the ragged dataset without difficulty Commented Mar 3, 2016 at 13:28
  • Similar question on SO: Selecting distinct values for multiple columns
    – Andriy M
    Commented Mar 7, 2016 at 17:46

2 Answers 2

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.

0

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.