Skip to Content
Author's profile photo Abhishek Agrawal

PIVOT Data in HANA

In my previous blog I have covered how to UNPIVOT data in HANA, in this blog I am covering how to PIVOT data in HANA.

While converting MS-SQL procedures into HANA procedures, we came across PIVOT statement in one of the select query, unfortunately HANA SQL Script does not support PIVOT operation hence we had to come up with alternative approach. In this blog I will cover two approaches to PIVOT data in HANA

  1. Using graphical calculation view
  2. Using HANA SQL Script

PIVOT Data using Graphical Calculation View:

Base Table: This is a PO header table which has Employee ID, Vendor ID and PO Number.

PIVOT Output: In pivot output lets say we want to know how many Purchase Orders placed by each employee, as Excel is the best tool to generate pivot output so I generated below output in excel:

We can get same output using graphical calculation view by following below steps:

  • Add a projection node and add base table in it:

 

  • Since there are 6 different employees in base table hence we need to create 6 calculated fields, one corresponding to each employee and these fields will become our pivot column header.
  • As we need count of PO for each employee we need to put below formula in each calculated field:

As per above expression, if  employee is “E1” then make E1 (new calculated field) = 1 else E1 = 0. Write same expression for all the other fields and change Emp_ID value to E2, E3.., etc.

Note: You need to create calculated fields in Projection node and not on Aggregation node otherwise aggregation on calculated fields will not happen.

Here is the output after creating all the calculated fields:

  • Connect projection node to Aggregation node and activate the view:

Pivoted Output:

 

In above use case we used Count as aggregation function however if you want to use Sum or Max as aggregated function then you just need to make small change in your calculated field formula and aggregation function in semantic node.

Changes for Sum: Lets say you want to check Sum of all POs placed by an employee, I know logically it does not make sense but I think you won’t mind if I take it as an example.

Make above change for all the calculated fields.

Output:

 

Changes for Max: 

Formula remains the same as Sum but you need to change aggregation function in Semantic node:

Output:

 

PIVOT Data using HANA SQLScript:

There are multiple ways of achieving Pivot functionality using HANA SQLScript

First Option:

Pivot with Count (replace Count with Sum in case you need Sum as aggregated function)

Pasting SQL just in case you want to copy:

select vendor_id, sum(“E1”) AS “E1”, SUM(“E2”) AS “E2”,SUM(“E3”) AS “E3”,SUM(“E4”) AS “E4”,SUM(“E5”) AS “E5”,
SUM(“E6”) AS “E6”
FROM
(
select vendor_id, COUNT(NUM) as “E1”, 0 AS “E2” , 0 AS “E3”, 0 AS “E4”, 0 AS “E5”, 0 AS “E6”
from “ABHISHEAGRAW”.”abhisheagraw::PIVOT_INITIAL_TABLE”
WHERE EMP_ID = ‘E1’ GROUP BY vendor_id
union all
select vendor_id, 0 AS “E1”, COUNT(NUM) as “E2”, 0 AS “E3”, 0 AS “E4”, 0 AS “E5”, 0 AS “E6”
from “ABHISHEAGRAW”.”abhisheagraw::PIVOT_INITIAL_TABLE”
WHERE EMP_ID = ‘E2’ GROUP BY vendor_id
union all
select vendor_id, 0 AS “E1”, 0 as “E2”, COUNT(NUM) AS “E3”, 0 AS “E4”, 0 AS “E5”, 0 AS “E6”
from “ABHISHEAGRAW”.”abhisheagraw::PIVOT_INITIAL_TABLE”
WHERE EMP_ID = ‘E3’ GROUP BY vendor_id
union all
select vendor_id, 0 AS “E1”, 0 as “E2”, 0 AS “E3”, COUNT(NUM) AS “E4”, 0 AS “E5”, 0 AS “E6”
from “ABHISHEAGRAW”.”abhisheagraw::PIVOT_INITIAL_TABLE”
WHERE EMP_ID = ‘E4’ GROUP BY vendor_id
union all
select vendor_id, 0 AS “E1”, 0 as “E2”, 0 AS “E3”, 0 AS “E4”, COUNT(NUM) AS “E5”, 0 AS “E6”
from “ABHISHEAGRAW”.”abhisheagraw::PIVOT_INITIAL_TABLE”
WHERE EMP_ID = ‘E5’ GROUP BY vendor_id
union all
select vendor_id, 0 AS “E1”, 0 as “E2”, 0 AS “E3”, 0 AS “E4”, 0 AS “E5”, COUNT(NUM) AS “E6”
from “ABHISHEAGRAW”.”abhisheagraw::PIVOT_INITIAL_TABLE”
WHERE EMP_ID = ‘E6’ GROUP BY vendor_id
) GROUP BY vendor_id ORDER BY vendor_id

 

Second Option: 

Pivot with Count (replace 1 with Num in case you need Sum as aggregated function)

SQL:

select vendor_id, sum(“E1”) AS “E1”, SUM(“E2”) AS “E2”,SUM(“E3”) AS “E3”,SUM(“E4”) AS “E4”,SUM(“E5”) AS “E5”,
SUM(“E6”) AS “E6”
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”,
CASE (EMP_ID) WHEN ‘E4’
THEN 1
ELSE 0
END as “E4”,
CASE (EMP_ID) WHEN ‘E5’
THEN 1
ELSE 0
END as “E5”,
CASE (EMP_ID) WHEN ‘E6’
THEN 1
ELSE 0
END as “E6”
from “ABHISHEAGRAW”.”abhisheagraw::PIVOT_INITIAL_TABLE”
)GROUP BY vendor_id ORDER BY vendor_id

 

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo N. Uppila
      N. Uppila

      Hi Abhishek,

       

      What happens if a new employee is added to the list ? Then we have to restructure the CV and rewrite the script as well, right ?

       

      Regards,
      Nithin

      Author's profile photo Vladislav Volodin
      Vladislav Volodin

      This is an important question. I like it and I know now what I should do 🙂

      You have got my "like".

      Author's profile photo Abhishek Agrawal
      Abhishek Agrawal
      Blog Post Author

      Hi Nithin,

      Unfortunately yes, we need to remodel and re-write the script and as far as I know even with PIVOT statement which is available in MS-SQL we need to add these PIVOT fields manually.

      Regards,

      Abhishek

       

      Author's profile photo prem kumar
      prem kumar

      Hello Abhishek,

      Thanks for the informative article. Can we simply transpose the records instead of count or sum . For example:

      EMP ID   Vendor 1  Vendor2

      E1            1000       500

      E1            2000       ( blank)

      E2           1000        2000

      E2           3000        4000

      To put it simple, there can be multiple vendors for same employee having different numbers. I need to change it to the column format based on the vendors and add as additional record for the same vendor but with different Number.

       

      Thank you.

      Regards,

      Prem

       

      Author's profile photo Abhishek Agrawal
      Abhishek Agrawal
      Blog Post Author

      Hey Prem,

       

      Can you please paste the expected output?

       

      Abhishek

      Author's profile photo prem kumar
      prem kumar

       

      Hello Abhishek,

      The table that I have mentioned in the previous comment is the expected output. Example :

      Current table format

      Employee      Vendor        Value

      EMP1            VEND1        100

      EMP1            VEND1        200

      EMP1           VEND2         300

      EMP2          VEND3          400

      EMP2          VEND4          500

      Expected output

      Employee     Vend1          Vend2    Vend3      Vend4

      EMP1           100               300        ( Blank)    ( Blank )

      EMP1           200              ( Blank )   ( Blank ) ( Blank )

      EMP2          ( Blank )        ( Blank )    400          500

       

      I have multiple information in the row format and would like to split it to column format. Can I use the logic that you have explained for this case as well or do you see some challenges implementing?

      Thank you.

      Regards,

      Prem

       

       

       

       

      Author's profile photo Abhishek Agrawal
      Abhishek Agrawal
      Blog Post Author

      Hey Prem,

       

      Sorry for delay in response, unfortunately above solution will not work for you, if you try above solution as is you will get output like this which is different from desired o/p. Let me work on this and will get back to you with solution.

      Author's profile photo Anup Kumar Rai
      Anup Kumar Rai

      Hi Prem,

      You can get the expected output if by using self join as shown below

       

      Select
      ActualTable.Employee,
      Vendor1.Value as Vend1,
      Vendor2.Value as Vend2,
      Vendor3.Value as Vend3
      from Employee as ActualTable
      left outer join Employee as Vendor1 on ActualTable.Employee=Vendor1.Employee
      and Vendor1.Vendor='VEND1'
      left outer join Employee as Vendor2 on ActualTable.Employee=Vendor2.Employee
      and Vendor2.Vendor='VEND2'
      left outer join Employee as Vendor3 on ActualTable.Employee=Vendor3.Employee
      and Vendor3.Vendor='VEND3'

       

      I am also looking for a more performant and elegant  approach. However the example shown above also solves the problem.

       

      Thanks

      -Anup-

      Author's profile photo Abhishek Desai
      Abhishek Desai

      Hello,

       

      Is it possible to Transpose Rows to columns, where as the columns might be dynamic.

       

      Ex: Input

      Col1 Col2 Col3
      1     a    10
      1     b    20
      1     c    30
      2     a    100
      2     b    200
      2     c    300
      

       

      And the output:

       

      Col1  a   b   c
      1     10  20  30
      2     100 200 300

       

      The Col2 values might increase dynamically.

      Author's profile photo Abhishek Agrawal
      Abhishek Agrawal
      Blog Post Author

      Hi Abhishek,

      Unfortunately dynamic creation of column is not possible with this approach, we have same limitation in SLQ PIVOT statement as well, columns need to be defined during design time.

       

      Regards,

      Abhishek