# Does vertica support derivative column concept in case statement.

HI,

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

edited January 2019

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:

```dbadmin=> SELECT * FROM tab1;
id | rank | columnname
----+------+------------
1 |    1 | A
1 |    2 | B
1 |    3 | C
1 |    4 | D
(4 rows)

dbadmin=> SELECT a.id, a.rank, a.columnname, listagg(b.columnname)
dbadmin->  GROUP BY a.id, a.rank, a.columnname;
id | rank | columnname | listagg
----+------+------------+---------
1 |    1 | A          | A
1 |    2 | B          | A,B
1 |    3 | C          | A,B,C
1 |    4 | D          | A,B,C,D
(4 rows)
```

Another option is to use the LAG analytic function:

```dbadmin=> SELECT * FROM tab1;
id | rank | columnname
----+------+------------
1 |    1 | A
1 |    2 | B
1 |    3 | C
1 |    4 | D
2 |    1 | X
2 |    2 | Y
2 |    3 | Z
(7 rows)

dbadmin->          THEN LAG(columnName) OVER (PARTITION BY id ORDER BY rank) || ',' || columnName
dbadmin->          THEN LAG(columnName, 2) OVER (PARTITION BY id ORDER BY rank) || ',' ||
dbadmin->            LAG(columnName) OVER (PARTITION BY id ORDER BY rank) || ',' || columnName
dbadmin->          THEN LAG(columnName, 3) OVER (PARTITION BY id ORDER BY rank) || ',' ||
dbadmin->            LAG(columnName, 2) OVER (PARTITION BY id ORDER BY rank) || ',' || LAG(columnName) OVER (PARTITION BY id ORDER BY rank) || ',' || columnName
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
2 |    1 | X          | X
2 |    2 | Y          | X,Y
2 |    3 | Z          | X,Y,Z
(7 rows)
```
• Thanks Jim
I will try both the approaches .