CODEWITHSUNDEEP
pivot-tableabapcds
user3312649
user3312649

Reputation: 300

Pivot/Unpivot table in ABAP CDS

Is it possible to do pivot query in ABAP CDS query? Is there a way we can achieve this like what we are always doing in SQL server and MySQL?

Upvotes: 1

Views: 2394

Answers (1)

Suncatcher
Suncatcher

Reputation: 10621

No, currently it is not supported natively neither in ABAP CDS nor in HANA.

There are several workarounds you can use to simulate pivot behavior:

  1. Pivoting with COUNT and UNION ALL [sample]

    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. Pivoting with COUNT through CASE [sample]

    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. Pivoting with CASE and grouping [sample]

The given snippets are HANA examples but they can be adapted to ABAP CDS. If you give a MCVE of your case, we can propose you a possible solution.

Upvotes: 1

Related Questions