The following example assumes the titanic dataset table exists:
DROP TABLE titanic_dataset_unpivoted;
CREATE MULTISET TABLE titanic_dataset_unpivoted (
passenger INTEGER,
AttributeName VARCHAR(128) CHARACTER SET UNICODE NOT CASESPECIFIC,
AttributeValue VARCHAR(20) CHARACTER SET UNICODE NOT CASESPECIFIC,
survived INTEGER
) PRIMARY INDEX ( passenger );
Insert into titanic_dataset_unpivoted(2, 'pclass', '1', 1);
Insert into titanic_dataset_unpivoted(2, 'gender', 'female', 1);
Insert into titanic_dataset_unpivoted(4, 'pclass', '1', 1);
Insert into titanic_dataset_unpivoted(4, 'gender', 'female', 1);
Insert into titanic_dataset_unpivoted(7, 'pclass', '1', 0);
Insert into titanic_dataset_unpivoted(7, 'gender', 'male', 0);
Insert into titanic_dataset_unpivoted(10, 'pclass', '2', 1);
Insert into titanic_dataset_unpivoted(10, 'gender', 'female', 1);
Insert into titanic_dataset_unpivoted(16, 'pclass', '2', 1);
Insert into titanic_dataset_unpivoted(16, 'gender', 'female', 1);
Insert into titanic_dataset_unpivoted(21, 'pclass', '2', 0);
Insert into titanic_dataset_unpivoted(21, 'gender', 'male', 0);
Insert into titanic_dataset_unpivoted(40, 'pclass', '3', 1);
Insert into titanic_dataset_unpivoted(40, 'gender', 'female', 1);
Insert into titanic_dataset_unpivoted(61, 'pclass', '3', 0);
Insert into titanic_dataset_unpivoted(61, 'gender', 'male', 0);
Insert into titanic_dataset_unpivoted(1000, 'pclass', '3', 1);
Insert into titanic_dataset_unpivoted(1000, 'gender', NULL, 1);
Calling TD_Pivoting with RowsPerPartition
SELECT * FROM TD_Pivoting (
ON titanic_dataset_unpivoted AS InputTable PARTITION BY passenger order by AttributeName
USING
PartitionColumns ('passenger')
TargetColumns ('AttributeValue')
Accumulate ('survived')
RowsPerPartition (2)
) AS dt Order By passenger;
TD_Pivoting Output
passenger AttributeValue_0 AttributeValue_1 survived --------- ---------------- ---------------- -------- 2 1 female 1 4 1 female 1 7 1 male 0 10 2 female 1 16 2 female 1 21 2 male 0 40 3 female 1 61 3 male 0 1000 3 ? 1