Skip to Content
-1

Question regarding Pivoting on Sap Hana

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Oct 12, 2018 at 07:03 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 12, 2018 at 12:38 PM

    have you also tried a Table Function? using SQLScript

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 16, 2018 at 12:32 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded