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