SAP Native HANA Procedure to Create and Populate a Transpose Table

Scenario:

The scenario is to transpose a table by a single transpose column and populate the new table with the data.

PRODUCT_DATA:

PRODUCT_TRANSPOSE:

Solution Approach:

The solution is to create the dynamic SQL statement as below which combines both creation and population of data into one step, where the bolded parts of the statement are dynamically created.

Create table Product_transpose as (        

    SELECT Product, Large,Small,Medium FROM (  

SELECT Product,

MAX(CASE WHEN Product_Type = 'Large' THEN Unit_Price END) AS   Large,

MAX(CASE WHEN Product_Type = 'Small' THEN Unit_Price END) AS Small,

MAX(CASE WHEN Product_Type = 'Medium' THEN Unit_Price END) AS Medium

From Product_data

Group By Product ))

First two statements are to create the table Product_transpose with the desired columns.

The inner select statement reads the data from product_data by transposing it as below.

Solution:

Step1:

First, we will use the SQL function “STRING_AGG”, to get the distinct values in the transpose column.

In our case “Product_Type”. This is the dynamic column list for the transpose table.

We will store this list it in  ‘ProductTypeList’ variable.

SELECT string_agg(PRODUCT_TYPE, ', ')
INTO ProductTypeList
FROM (SELECT DISTINCT PRODUCT_TYPE from PRODUCT_DATA as b);

Step2:

Create the first part of the dynamic SQL statement as below.

DynamicSQL := 'Create table Product_transpose as (

    SELECT PRODUCT,  ' || ProductTypeList || ' FROM (

        SELECT PRODUCT,

         ';

Step3:

Dynamically create the statement for populating the data as below.

SELECT 
STRING_AGG('MAX(CASE WHEN PRODUCT_TYPE = ''' || PRODUCT_TYPE || ''' THEN 
Unit_Price END) AS ' || PRODUCT_TYPE, ', ')

INTO ColumnExpressions

FROM (SELECT DISTINCT PRODUCT_TYPE FROM Product_data);

Step4:

Combine all the 3 steps and create the complete dynamic SQL.

DynamicSQL := DynamicSQL || ColumnExpressions || 
' FROM Product_data GROUP BY PRODUCT))';

Step5:

Execute the dynamic SQL.

EXEC DynamicSQL;

Step6:

Call the procedure from the SQL console as below.

CALL "MYSCHEMA"."MYPACKAGE::product_transpose" ( ) ;

Complete Code:

PROCEDURE "MYSCHEMA"."MYPACKAGE::product_transpose" ( )

       LANGUAGE SQLSCRIPT

       SQL SECURITY INVOKER

       --DEFAULT SCHEMA <default_schema_name>

       --READS SQL DATA

       AS

BEGIN

DECLARE ProductTypeList NVARCHAR(10000);

DECLARE DynamicSQL NVARCHAR(60000);

DECLARE ColumnExpressions NVARCHAR(50000);


SELECT string_agg(PRODUCT_TYPE, ', ')

INTO ProductTypeList

FROM (SELECT DISTINCT PRODUCT_TYPE from PRODUCT_DATA as b);


DynamicSQL := 'Create table Product_Transpose as (

    SELECT PRODUCT, ' || ProductTypeList || '

    FROM (

        SELECT PRODUCT,

   ';

SELECT STRING_AGG('MAX(CASE WHEN PRODUCT_TYPE = ''' || PRODUCT_TYPE || ''' 

       THEN UNIT_PRICE END) AS ' || PRODUCT_TYPE, ', ')

       INTO ColumnExpressions

      FROM (SELECT DISTINCT PRODUCT_TYPE FROM PRODUCT_DATA);
   

DynamicSQL := DynamicSQL || ColumnExpressions || ' FROM PRODUCT_DATA

    GROUP BY PRODUCT))';

EXEC DynamicSQL;


END;

In Conclusion,

SAP Native HANA procedures are a powerful feature to execute complex data transformations, which can significantly enhance data manipulation and processing efficiency.

Embrace the advantages of streamlined data processing, optimized performance, and simplified code maintenance that these procedures offer.

Feel free to like, comment and share your thoughts! Your insights, questions, and feedback are invaluable in developing a collaborative learning environment.