How to Convert SQL Unpivot Query to Hana SQL

How to Unpivot in SQL? (SAP HANA) (Columns to rows)

Here is a solution that requires me to specify the column names and type conversions:

(Credit to jarlh for the union suggestion)

select 
"ID",
'Country' as "FieldName",
"COUNTRY" as "FieldValue"
from #example
union all
select 
"ID",
'Name' as "FieldName",
"NAME" as "FieldValue"
from #example
union all
select 
"ID",
'Balance' as "FieldName",
CAST("BALANCE" as NVARCHAR) as "FieldValue"
from #example

How to get the transpose in sql (HANA)?

A pure SQL solution (SQL Server)

select 
  [A] 
  , [B]
  , [C]
from
(
  select Type,[Count]
  from ( SELECT (CASE WHEN type=1 THEN 'A' 
             WHEN type=2 THEN 'B' 
             .....   
       END) as TYPE,COUNT(*) AS COUNT
        from TYPE_TABLE GROUP BY TYPE) Table1
) x
pivot
(
  SUM(Count)
  for Type in([A], [B], [C])
)p

Demo

Pivot/Unpivot table in ABAP CDS

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.

---

Related Topics