cancel
Showing results for 
Search instead for 
Did you mean: 

Dynamically pivoting a table in SAP HANA

Former Member
0 Kudos

In this thread

http://scn.sap.com/thread/3527126

OP asks for an option to pivot a table in HANA.

The solution works, but I am interested in a dynamic solution so that it automatically adapts to the structure of the table and the columns names do not need to be created manually everytime a table needs to be pivoted...something like this for MySQL: http://buysql.com/mysql/14-how-to-automate-pivot-tables.html

SET @sql = NULL;

SELECT

  GROUP_CONCAT(DISTINCT

    CONCAT(

      'MAX(IF(property_name = ''',

      property_name,

      ''', value, NULL)) AS ',

      property_name

    )

  ) INTO @sql

FROM properties;

SET @sql = CONCAT('SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id');

I have read the SCN forum and I do not want to do anything involving a calculation view or work with Data Services. Is there a way to do that? 

Accepted Solutions (1)

Accepted Solutions (1)

mfath
Advisor
Advisor
0 Kudos

Hi,
I used this"ugly" (dynamic SQL) procedure below to transpose the output of Text Analysis in HANA.

The procedure may not be as flexible as required, but at least it is dynamic.
It also concatenates multi values. You may want to get rid of this STRING_AGG piece in the query.
Furthermore, the procedure creates all columns as VARCHAR(5000) which is of course not appropriate when dealing with numbers.
Performance is ok, at least it is sufficient for my purposes. It takes about 15 sec to transpose 5 mio. rows to 65k rows and 63 columns.
Regards, Markus

-- THE DATA
CREATE SCHEMA "TEST";
SET SCHEMA "TEST";
DROP TABLE "DOCUMENTS";
CREATE COLUMN TABLE "DOCUMENTS"(
"ID"  INTEGER PRIMARY KEY,
"CONTENT" VARCHAR(5000)
);
INSERT INTO "DOCUMENTS" VALUES (1,'I am Batman and I work for Wayne Enterprises. Spiderman is my friend.');
CREATE FULLTEXT INDEX "FTI_DOCUMENTS_CONTENT" ON "DOCUMENTS"("CONTENT") TEXT ANALYSIS ON CONFIGURATION 'EXTRACTION_CORE';

SELECT * FROM "$TA_FTI_DOCUMENTS_CONTENT";

DROP VIEW "V_TA";
CREATE VIEW "V_TA" AS (SELECT "ID" AS "ID", "TA_RULE", "TA_TOKEN", "TA_TYPE" FROM "$TA_FTI_DOCUMENTS_CONTENT");

-- THE LOGIC
SELECT T."ID", T."TA_RULE" ,"PERSON","ORGANIZATION/COMMERCIAL"
FROM (SELECT DISTINCT "ID","TA_RULE" FROM "TEST"."V_TA") AS T 
LEFT JOIN (SELECT "ID","TA_RULE",TO_CLOB(STRING_AGG("TA_TOKEN",'|')) AS "PERSON" 
FROM (SELECT DISTINCT "ID","TA_RULE","TA_TOKEN" FROM "TEST"."V_TA" WHERE "TA_TYPE" = 'PERSON') GROUP BY "ID", "TA_RULE") AS T0
ON T."ID" = T0."ID" AND T."TA_RULE" = T0."TA_RULE" 
LEFT JOIN (SELECT "ID","TA_RULE",TO_CLOB(STRING_AGG("TA_TOKEN",'|')) AS "ORGANIZATION/COMMERCIAL" 
FROM (SELECT DISTINCT "ID","TA_RULE","TA_TOKEN" FROM "TEST"."V_TA" WHERE "TA_TYPE" = 'ORGANIZATION/COMMERCIAL') GROUP BY "ID", "TA_RULE") AS T1
ON T."ID" = T1."ID" AND T."TA_RULE" = T1."TA_RULE";

-- THE PROCEDURE
CREATE GLOBAL TEMPORARY TABLE "TEST"."TRANSPOSE_DYN_OUT" ("ID" INTEGER);-- just a dummy

DROP PROCEDURE "TRANSPOSE_DYN";
CREATE PROCEDURE "TRANSPOSE_DYN" (IN i_limit INTEGER, OUT o_sql CLOB)
LANGUAGE SQLSCRIPT
AS BEGIN
DECLARE v_i INTEGER := 0;
DECLARE v_col_list CLOB := '';
DECLARE v_col_list2 CLOB := '';
DECLARE v_select CLOB := '';
DECLARE v_sql CLOB := '';
DECLARE CURSOR c_name FOR SELECT "TA_TYPE" AS "NAME" FROM "TEST"."V_TA" GROUP BY "TA_TYPE" ORDER BY COUNT(*) DESC LIMIT :i_limit; -- get the columns
FOR cur_row AS c_name DO
  v_col_list := :v_col_list || ',"'||cur_row.NAME||'"';
  v_col_list2 := :v_col_list2 || ',"'||cur_row.NAME||'" VARCHAR(5000)';
  v_select := :v_select || ' LEFT JOIN (SELECT "ID", "TA_RULE", TO_VARCHAR(STRING_AGG("TA_TOKEN",''|'')) AS "'||cur_row.NAME||'" ';
  v_select := :v_select || ' FROM (SELECT DISTINCT "ID", "TA_RULE", "TA_TOKEN" FROM "TEST"."V_TA" WHERE "TA_TYPE" = '''||cur_row.NAME||''') GROUP BY "ID", "TA_RULE") AS T'||:v_i;
  v_select := :v_select || ' ON "T"."ID" = "T'||:v_i||'"."ID" AND "T"."TA_RULE" = "T'||:v_i||'"."TA_RULE" ' ; 
  v_i := :v_i + 1;
END FOR;
EXEC 'DROP TABLE "TEST"."TRANSPOSE_DYN_OUT"';
EXEC 'CREATE GLOBAL TEMPORARY TABLE "TEST"."TRANSPOSE_DYN_OUT"("ID" VARCHAR(5000), "TA_RULE" VARCHAR(5000)' || :v_col_list2 || ')';
v_sql := 'SELECT "T"."ID", "T"."TA_RULE" '||:v_col_list||' FROM (SELECT DISTINCT "ID", "TA_RULE" FROM "TEST"."V_TA") AS T ' || :v_select || ' INTO "TEST"."TRANSPOSE_DYN_OUT"';
EXEC :v_sql;
o_sql := :v_sql;
END;

CALL "TEST"."TRANSPOSE_DYN"(10,?); --IN LIMIT, OUT SQL
SELECT * FROM "TEST"."TRANSPOSE_DYN_OUT";

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

No, there isn't. Not in any revision released so far (up to rev. 101).

- Lars

Former Member
0 Kudos

Thanks for your answer. I just would like to clarify: I know there is no "Make Pivot Table" Command. I wonder whether there is any scripting option, maybe even using Perl, to generate a SQL Code to do what I want. Thanks.

lbreddemann
Active Contributor
0 Kudos

You could - obviously - go and write a stored procedure that would change the table structure.

Or you can go and create a data flow with the enterprise information lifecycle management option. (which would be similar to DataServices).

And of course: with any client language able to emit SQL you can do whatever you want.

Not sure why this would be PERL in your case, but that's your choice.

- Lars

Former Member
0 Kudos

Hey Lars, what would it take to put it in rev. 102? all workarounds seem somewhat laborious and behind the competition.

rgds, gm

lbreddemann
Active Contributor
0 Kudos

I don't know what it would take.

Seems to me like a question for the SAP HANA PM.

Pinging  Former Member and on this matter.

- Lars

ruediger_karl
Advisor
Advisor
0 Kudos

PIVOT/UNPIVOT constructs have been on the long list for HANA. It is not planned for the next upcoming revisions or SPS11 version.

Regards, Ruediger