Does vertica support derivative column concept in case statement.
Please help me out as I am stuck with a Sql Case statement in vertica.
I have a a data set where
ID | rank | Columnname
1 | 1 | A
1 | 2 | B
1 | 3 | C
1 | 4 | D
My Desired Output :
ID | rank | Columnname | Aggregate
1 | 1 | A | A
1 | 2 | B | A,B
1 | 3 | C | A,B,C
1 | 4 | D | A,B,C,D
The aggregate column should concatenate the columnname values based on the rank.
For example For rank =2 Aggregate value should be Columnname in rank 1+ Columnname in Seq 2.
For rank = 3 Aggregate value should be Columnname in rank 1+ Columnname in rank 2 (Or derivative obtain from last statement )+ Columnname in rank 2. and so on
I have written a CASE statement to handle this . But This is not giving the result as expected since for every rank it is re-evaluating the case instead of taking result from first iteration.
(CASE WHEN rank = 1 THEN ColumnName END) ||
(CASE WHEN rank = 2 THEN ', ' || ColumnName ELSE '' END) ||
(CASE WHEN rank = 3 THEN ', ' || ColumnName ELSE '' END) AS Aggregate
The problem with this is from rank =2 I GET NULL VALUES FOR AGGREGATE.
NOTE= Rank can go upto 25.
Please let me know if anyone has a solution to this.