Does vertica support derivative column concept in case statement.
HI,
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.
SELECT
ID,rank,columnname
(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
FROM Table;
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.
Thanks
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
Comments
What version of Vertica are you running?
It's relatively easy to accomplish your goal using the LISTAGG function which is available in Vertica 9.1.1-4 and above.
Example:
See:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/LISTAGG.htm
Another option is to use the LAG analytic function:
I will try both the approaches .