Question regarding Pivoting on Sap Hana

[DKK]

Hello,

I am trying to create a procedure which will receive a table and then transpose the rows to columns according to the values that it will receive. I manage to implement this , but due to large amount of values (= columns) , it takes almost 10 minutes to be executed.

Except the usage of analytic view , which cannot be dynamic with the names of the column or the number/name of the values to be transported , can anyone suggest a faster way to implement it. It is really sad that for this simple exercise other Databases have already implement this several years ago.

Thank you.

[DKK]

Hello,

Thank you both for your answers , i manage to create a proc which run on couple of seconds ,

CREATE  procedure PIVOT_TABLE(IN SCHEMA_NAME VARCHAR(100),IN TABLE_NAME VARCHAR(100))
LANGUAGE SQLSCRIPT 
AS
BEGIN
---------------------------------------------------------------------------------------------
--CREATES THE PIVOT TABLE FROM THE INSERTED AS PIVOT_#TABLE_NAME_INSERTED ON THE SAME SCHEMA--
--THIS IS ONLY FOR ONE METRIC
----------------------------------------------------------------------------------------------
DECLARE v_count INT DEFAULT 1 ;
DECLARE STEP0 VARCHAR(100000)DEFAULT ' ' ;  
DECLARE STEP1 VARCHAR(100000) DEFAULT ' ' ; 
DECLARE STEP4 VARCHAR(100) ;   
DECLARE STEP5 VARCHAR(1000) DEFAULT ' ' ;   
DECLARE STEP6 VARCHAR(1000000) DEFAULT ' ' ;   
DECLARE STEP7 VARCHAR(1000);  
DECLARE rn_ INTEGER ;

declare IN_SCHEMA_NAME VARCHAR(100) := :SCHEMA_NAME;
declare IN_TABLE_NAME VARCHAR(100) :=  :TABLE_NAME;
exec 'set schema '||:IN_SCHEMA_NAME;

CREATE table "VARIABLE_"(RN2 INTEGER,VARIABLE INTEGER);
drop table "VARIABLE_";
CREATE table "rn_"(rn_ INTEGER);

   select   'INSERT INTO "rn_" SELECT COUNT(DISTINCT "'||column_name||'")  from "' || :IN_TABLE_NAME || '";'  
   into step4
 from TABLE_COLUMNS
 where   TABLE_NAME = :IN_TABLE_NAME
  AND SCHEMA_NAME = :IN_SCHEMA_NAME 
 and position  = 2;

  EXEC :STEP4;
  
  SELECT rn_
  INTO rn_
  FROM "rn_";
  
 SELECT 
'CREATE COLUMN TABLE VARIABLE_ AS (SELECT row_number()over(order by '||
(select '"'||column_name||'"' from TABLE_COLUMNS
wherE SCHEMA_NAME =  :IN_SCHEMA_NAME AND TABLE_NAME = :IN_TABLE_NAME and position  = 2)||') AS "RN2",
  "'||(select column_name from TABLE_COLUMNS
wherE SCHEMA_NAME =  :IN_SCHEMA_NAME AND TABLE_NAME = :IN_TABLE_NAME and position  = 2)||'" AS "VARIABLE" FROM (
  SELECT DISTINCT "'||(select column_name from TABLE_COLUMNS
wherE SCHEMA_NAME =  :IN_SCHEMA_NAME AND TABLE_NAME = :IN_TABLE_NAME and position  = 2)||'" 
FROM "'||:IN_SCHEMA_NAME||'"."'||:IN_TABLE_NAME||'")) ;'
INTO STEP5
FROM DUMMY;

EXEC :STEP5 ;

 WHILE rn_ >= :V_COUNT  DO 
SELECT
',MAX(CASE WHEN RN2 = '||:v_count||' THEN "'||(SELECT COLUMN_NAME FROM (
select POSITION,column_name ,MAX(position)OVER()  AS XX
from table_COLUMNS  
where   TABLE_NAME = :IN_TABLE_NAME AND SCHEMA_NAME = :IN_SCHEMA_NAME 
)WHERE XX = POSITION)||'" END ) AS "'||
(select VARIABLE from "VARIABLE_" where rn2 = :v_count)
||'"
'
INTO STEP0
from dummy;

STEP1 := STEP1||:STEP0;

v_count := v_count + 1;
end while;

SELECT 'CREATE COLUMN TABLE TARGET_ AS (SELECT "'||(
SELECT COLUMN_NAME from table_COLUMNS  
where   TABLE_NAME = :IN_TABLE_NAME  AND SCHEMA_NAME = :IN_SCHEMA_NAME AND POSITION = 1
)||'" '||:STEP1
||'FROM (
SELECT A.*,ROW_NUMBER()OVER(PARTITION BY "'||
(SELECT COLUMN_NAME from table_COLUMNS  
where   TABLE_NAME = :IN_TABLE_NAME  AND SCHEMA_NAME = :IN_SCHEMA_NAME AND POSITION = 1)||'" ORDER BY "'||
(SELECT COLUMN_NAME from table_COLUMNS  
where   TABLE_NAME = :IN_TABLE_NAME AND POSITION = 2)
||'") AS "RN2" FROM "'||:IN_SCHEMA_NAME||'"."'||:IN_TABLE_NAME||'" A
)
GROUP BY "'||
(
SELECT COLUMN_NAME from table_COLUMNS  
where   TABLE_NAME = :IN_TABLE_NAME AND SCHEMA_NAME = :IN_SCHEMA_NAME AND POSITION = 1
)||'") ;'
INTO STEP6
 FROM DUMMY;
 
 EXEC :STEP6;
 
 
exec 'set schema '||:IN_SCHEMA_NAME;
drop table "VARIABLE_";
DROP table "rn_";
end;

Thank you once more for your time.

[SergioG_TX]

have you also tried a Table Function? using SQLScript

[former_member45380]

Hi Dimitrios,

have you tried the Pivot function in SAP HANA Flowgraphs? I use it for static transformation when I know which columns I want to have as pivot columns and it works fine. However, I'm not sure if you can choose the pivot columns dynamically but maybe it's worth trying.

Best,

Felix