Vertica: How Can You Concate Values By Some Order?

Suppose you have columns ID | A | B | C 1 | 3 | 1 | 2 2 | 5 | 9 | 1 3 | 1 | 2 | 3 and you want the columns concatenated such that the end result would look like ID | ABC_value

Solution 1:

You can do this with a nasty case expression. For three columns it is not so bad:

select t.*,
       (gr ||','||
        (casewhen a notin (le, gr) then a
              when b notin (le, br) then b
              else c
         end) ||','||
        le
       ),
       ((case gr when a then'a'when b then'b'else'c'end) ||','||
        (casewhen a notin (gr, le) then'a'when b notin (gr, le) then'b'else'c'end) ||','||
        (case le when a then'a'when b then'b'else'c'end)
       )          
from (select t.*, greatest(a, b, c) as gr, least(a, b, c) as le
      from t
     ) t;

This particular version assumes there are no duplicates or NULL values, although this can be adopted for that purpose.