My CDS view delivers a result like this.
* RESULT CDS VIEW
*----------------------------
*|CHARG|Z_F1 |Z_F2 |Z_F3 |
*----------------------------
*|011 |VAL F1| | |
*|011 | |VAL F2| |
*|011 | | |VAL F3|
*----------------------------
I want to achieve something like a MySQL group_by CHARG resulting in just 1 row like this:
* EXPECTED RESULT
*----------------------------
*|CHARG|Z_F1 |Z_F2 |Z_F3 |
*----------------------------
*|011 |VAL F1|VAL F2|VAL F3|
*----------------------------
How to do that in abap sql / cds views? It expects all the fields in the group_by clause.
I am looking for a CDS solution without abap coding.
I forgot to mention that the Z_F1 to Z_F3 fields are a pivot result of the same column, that's why they are in different rows at first place.
The cds looks like this:
... as select...{
charg,
case when _f1 = '1' then 'VAL F1' else '' end as Z_F1,
case when _f1 = '2' then 'VAL F2' else '' end as Z_F2,
case when _f1 = '3' then 'VAL F3' else '' end as Z_F3
...
}
After a lot of research, I finally found a way to merge multiple rows into one row in this article:
Transpose Or Merge Multiple Rows In One Row CDS Views
The solution is, as expected a group_by clause of just the 1 field all rows share.
You can use the SQL MAX() function to use group_by without the Z_F1 to 3 fields.
{
charg,
max( case when _f1 = '1' then 'VAL F1' end ) as Z_F1,
max( case when _f1 = '2' then 'VAL F2' end ) as Z_F2,
max( case when _f1 = '3' then 'VAL F3' end ) as Z_F3
}
group by charg
resulting in:
*----------------------------
*|CHARG|Z_F1 |Z_F2 |Z_F3 |
*----------------------------
*|011 |VAL F1|VAL F2|VAL F3|
*----------------------------