By hhh


2019-04-26 10:58:43 8 Comments

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_DESC | ABC_value_DESC_colnames
1  | 3,2,1          | A,C,B
2  | 9,5,1          | B,A,C
3  | 3,2,1          | C,B,A 

where you want to get the col values in Descending order within the new column ABC_value_DESC and then return corresponding name of column in the new column ABC_value_DESC_colnames.

How can you do the concatenation of values of multiple columns into a new column in Descending order and return column names by value order (not name order) in Vertica 9?


Ps. I have tried Listagg -function but bugs such that ordering not implemented and when tried Vertica's suggestion here giving false result and even bugs with alternative here.

1 comments

@Gordon Linoff 2019-04-26 11:06:01

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

select t.*,
       (gr || ',' ||
        (case when a not in (le, gr) then a
              when b not in (le, br) then b
              else c
         end) || ',' ||
        le
       ),
       ((case gr when a then 'a' when b then 'b' else 'c' end) || ',' ||
        (case when a not in (gr, le) then 'a'
              when b not in (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.

Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

59 Answered Questions

[SOLVED] How can I concatenate two arrays in Java?

16 Answered Questions

[SOLVED] How do I limit the number of rows returned by an Oracle query after ordering?

16 Answered Questions

[SOLVED] How can I do an UPDATE statement with JOIN in SQL?

1 Answered Questions

1 Answered Questions

1 Answered Questions

[SOLVED] VERTICA insert multiple rows in one statement with named columns

  • 2018-08-22 13:38:07
  • Dschoni
  • 555 View
  • 2 Score
  • 1 Answer
  • Tags:   sql vertica

7 Answered Questions

1 Answered Questions

[SOLVED] Select column values alternatively with predefined value order

  • 2015-11-30 11:22:09
  • user5620899
  • 218 View
  • 3 Score
  • 1 Answer
  • Tags:   sql postgresql

1 Answered Questions

[SOLVED] How do you JOIN tables to a view using a Vertica DB?

  • 2014-08-11 18:49:50
  • Mr. Spock
  • 613 View
  • 2 Score
  • 1 Answer
  • Tags:   sql vertica

Sponsored Content