ABAP CDS 中的透视/取消透视表

Question

是否可以在ABAP CDS查询中进行透视查询？有没有办法像我们在SQL Server和MySQL中所做的那样实现这一点？

Answer 1

不，目前在 ABAP CDS 和 HANA 中都不支持它。

有几种解决方法可用于模拟透视行为：

1. 使用COUNT和UNION ALL旋转 [示例]

   select vendor_id, sum("E1") AS "E1", SUM("E2") AS "E2", SUM("E3") AS "E3"
   FROM
   (
   select vendor_id, COUNT(NUM) as "E1", 0 AS "E2" , 0 AS "E3"
   from "ABHISHEAGRAW".
   WHERE EMP_ID = 'E1' GROUP BY vendor_id
   union all
   select vendor_id, 0 AS “E1”, COUNT(NUM) as "E2", 0 AS "E3"
   from "ABHISHEAGRAW".
   WHERE EMP_ID = 'E2' GROUP BY vendor_id
   union all
   select vendor_id, 0 AS “E1”, 0 as "E2", COUNT(NUM) AS "E3"
   from "ABHISHEAGRAW".
   WHERE EMP_ID = 'E3' GROUP BY vendor_id
   ) GROUP BY vendor_id ORDER BY vendor_id
   

2. 通过CASECOUNT旋转 [示例]

   select vendor_id, sum("E1") AS "E1", SUM("E2") AS "E2", SUM("E3") AS "E3"
   FROM
   (
   select vendor_id, CASE (EMP_ID) WHEN 'E1'
   THEN 1
   ELSE 0
   END as "E1",
   CASE (EMP_ID) WHEN 'E2'
   THEN 1
   ELSE 0
   END as "E2",
   CASE (EMP_ID) WHEN 'E3'
   THEN 1
   ELSE 0
   END as "E3"
   from “ABHISHEAGRAW”.
   )GROUP BY vendor_id ORDER BY vendor_id
   

3. 使用 CASE 和分组进行旋转 [示例]

给定的代码片段是 HANA 示例，但它们可以适应 ABAP CDS。如果您提供案例的MCVE，我们可以为您提供可能的解决方案。