Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

 

 
Labels in this area