SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for SAP HANA and ABAP, HANA Database, SQLScript, SAP UI5, Screen Personas, Web Dynpro, Workflow

SQL Pivot Table in SAP HANA Database using SQLScript


SAP HANA database developers can convert rows into columns using SQL Pivot Table queries in SQLScript. This SQLScript pivot table tutorial shows how to build sample pivot table query on your HANA database easily.

Pivot Query using SQLScript on HANA Database

Let's first create our sample HANA database table as column table with following CREATE TABLE command.

-- sample database table with transactional data
create column table Orders (
 OrderId int generated by default as identity not null,
 Product varchar(10),
 Variant varchar(10),
 Quantity int
);
Code

As seen in database table field structure, ABAP programmer may want to build a SQLScript Select statement that will display the Variant values in seperate columns for each product.

By assuming product variant is based on colors, we can provide sample data into HANA database table Orders using following SQL Insert statements

-- sample data populated with Insert commands
insert into Orders(Product, Variant, Quantity) values ('Caps','Red',5);
insert into Orders(Product, Variant, Quantity) values ('Helmets','Blue',10);
insert into Orders(Product, Variant, Quantity) values ('Helmets','White',5);
insert into Orders(Product, Variant, Quantity) values ('Helmets','Red',20);
insert into Orders(Product, Variant, Quantity) values ('Caps','Red',15);
insert into Orders(Product, Variant, Quantity) values ('Chapeau','White',10);
insert into Orders(Product, Variant, Quantity) values ('Helmets','White',5);
insert into Orders(Product, Variant, Quantity) values ('Chapeau','Red',30);
Code

By executing SQL pivot table query on HANA database sample table above, developers can convert tabular table data into pivot table view as follows:

tabular data to pivot table using SQLScript on HANA database

Unfortunately, if dynamic pivot table query is not used database developer should define the column names which are corresponding values of column values explicitely as seen in following SQLScript code.

SQL CASE command enables moving quantity value under a new field for that variant color.
If database developer is interested in only 5 colors, they can be listed in SELECT fields as seperate columns using CASE statement.

select
 Product,
 case when Variant = 'Blue' then Quantity else NULL end as "Blue",
 case when Variant = 'Red' then Quantity else NULL end as "Red",
 case when Variant = 'White' then Quantity else NULL end as "White"
from Orders;
Code

Of course, if database developer execute above SQL SELECT statement as seen in below screenshot the result cannot be said to be a SQL pivot query

SQL CASE for pivot query on HANA database

To complete SQLScript Pivot query example, SQL programmers can use SUM() aggregate function based on Product field so that total quantity for each product color is displayed under the column name for that variant.

select
 Product,
 SUM(case when Variant = 'Blue' then Quantity else NULL end) as "Blue",
 SUM(case when Variant = 'Red' then Quantity else NULL end) as "Red",
 SUM(case when Variant = 'White' then Quantity else NULL end) as "White"
from Orders
group by Product;
Code

Here is the output of the HANA databasee pivor query using SQLScript sample.

SQL pivot table query on SAP HANA database

Of course, if the pivot table columns are not known before or the pivot columns are too many for SQL developer to code as SQLScript, dynamic pivot queries should be build and executed on HANA database.



SAP HANA and ABAP

Install SAP Free
CRM Companies List
Web Based CRM Software


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.