on 07-30-2015 7:39 AM
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?
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";
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
No, there isn't. Not in any revision released so far (up to rev. 101).
- Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
83 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.