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
);
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);
By executing SQL pivot table query on HANA database sample table above, developers can convert tabular table data into pivot table view as follows:
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;
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
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;
Here is the output of the HANA databasee pivor query using SQLScript sample.
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.
