英文:
abap sql / CDS- how to get multiple rows into a single row (group by)?
问题
我的CDS视图提供了以下结果。
* RESULT CDS VIEW
*----------------------------
*|CHARG|Z_F1 |Z_F2 |Z_F3 |
*----------------------------
*|011 |VAL F1| | |
*|011 | |VAL F2| |
*|011 | | |VAL F3|
*----------------------------
我想要实现像MySQL的group_by CHARG那样的结果，只有一行，像这样：
* EXPECTED RESULT
*----------------------------
*|CHARG|Z_F1 |Z_F2 |Z_F3 |
*----------------------------
*|011 |VAL F1|VAL F2|VAL F3|
*----------------------------
如何在ABAP SQL / CDS视图中实现这一点？它要求在group_by子句中包含所有字段。
我正在寻找一种不需要ABAP编码的CDS解决方案。
我忘了提到Z_F1到Z_F3字段是同一列的透视结果，这就是它们一开始在不同行的原因。
CDS视图的代码如下：
... 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
...
}
英文:
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
...
}
答案1
得分: 1
在进行了大量研究之后，我终于找到了一种将多行合并为一行的方法，可以在这篇文章中找到详细解释：
解决方案是使用一个只包含所有行共享的字段的 group_by 子句。您可以使用 SQL 的 MAX() 函数来在没有 Z_F1 到 Z_F3 字段的情况下使用 group_by。
{
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
结果如下：
*----------------------------
*|CHARG|Z_F1 |Z_F2 |Z_F3 |
*----------------------------
*|011 |VAL F1|VAL F2|VAL 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|
*----------------------------
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论