InputTable
The following contains a subset of titanic dataset.
DROP TABLE unpivoting_titanic_dataset; CREATE TABLE unpivoting_titanic_dataset ( passenger INTEGER, survived INTEGER, pclass INTEGER, name VARCHAR(90) CHARACTER SET LATIN NOT CASESPECIFIC, gender VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC, age INTEGER, sibsp INTEGER, parch INTEGER, ticket VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC, fare FLOAT, cabin VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC, embarked VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC) PRIMARY INDEX ( passenger ); INSERT INTO unpivoting_titanic_dataset (2, 1, 1, 'Cumings; Mrs. John Bradley (Florence Briggs Thayer)', 'female', 38, 1, 0, 'PC 17599', 71.2833, 'C85', 'C'); INSERT INTO unpivoting_titanic_dataset (4, 1, 1, 'Futrelle; Mrs. Jacques Heath (Lily May Peel)', 'female', 35, 1, 0, '113803', 53.1, 'C123', 'S'); INSERT INTO unpivoting_titanic_dataset (7, 0, 1, 'McCarthy; Mr. Timothy J', 'male', 54, 0, 0, '17463', 51.8625, 'E46', 'S'); INSERT INTO unpivoting_titanic_dataset (10, 1, 2, 'Nasser; Mrs. Nicholas (Adele Achem)', 'female', 14, 1, 0, '237736', 30.0708, '', 'C'); INSERT INTO unpivoting_titanic_dataset (16, 1, 2, 'Hewlett; Mrs. (Mary D Kingcome) ', 'female', 55, 0, 0, '248706', 16, '', 'S'); INSERT INTO unpivoting_titanic_dataset (21, 0, 2, 'Fynney; Mr. Joseph J', 'male', 35, 0, 0, '239865', 26, '', 'S'); INSERT INTO unpivoting_titanic_dataset (40, 1, 3, 'Nicola-Yarred; Miss. Jamila', 'female', 14, 1, 0, '2651', 11.2417, '', 'C'); INSERT INTO unpivoting_titanic_dataset (61, 0, 3, 'Sirayanian; Mr. Orsen', 'male', 22, 0, 0, '2669', 7.2292, '', 'C'); INSERT INTO unpivoting_titanic_dataset (1000, 1, 3, 'ABC', NULL, 30, 0, 0, '00000', 100.50, '', 'S');
SQL Call 1
SELECT * FROM TD_UNPIVOTING(
ON unpivoting_titanic_dataset AS InputTable PARTITION BY ANY
USING
IDCOLUMN('passenger')
TARGETCOLUMNS ('gender')
ACCUMULATE ('survived')
INCLUDENULLS('true')
)AS dt ORDER BY 2, 1;
Output 1
passenger AttributeName AttributeValue survived --------- ------------- -------------- -------- 2 gender female 1 4 gender female 1 7 gender male 0 10 gender female 1 16 gender female 1 21 gender male 0 40 gender female 1 61 gender male 0
SQL Call 2
SELECT * FROM TD_UNPIVOTING(
ON unpivoting_titanic_dataset AS InputTable PARTITION BY ANY
USING
IDCOLUMN('passenger')
TARGETCOLUMNS ('gender')
ATTRIBUTEALIASLIST ('gender_titanic')
ATTRIBUTECOLNAME('Attribute')
VALUECOLNAME('value')
ACCUMULATE ('survived')
INCLUDENULLS('true')
)AS dt ORDER BY 2, 1;
Output 2
passenger Attribute Value survived --------- --------- ----- -------- 2 gender_titanic female 1 4 gender_titanic female 1 7 gender_titanic male 0 10 gender_titanic female 1 16 gender_titanic female 1 21 gender_titanic male 0 40 gender_titanic female 1 61 gender_titanic male 0 1000 gender_titanic ? 1
SQL Call 3
SELECT * FROM TD_UNPIVOTING(
ON unpivoting_titanic_dataset AS InputTable PARTITION BY ANY
USING
IDCOLUMN('passenger')
TARGETCOLUMNS ('gender','embarked')
ATTRIBUTECOLNAME('Attribute')
VALUECOLNAME('value')
ACCUMULATE ('survived')
INCLUDENULLS('true')
INDEXEDATTRIBUTE('true')
INCLUDEDATATYPES('true')
)AS dt ORDER BY 2, 1;
Output 3
passenger Attribute Value DataTypes survived --------- --------- ------ --------------------------------- -------- 2 2 female VARCHAR(10) CHARACTER SET UNICODE 1 4 2 female VARCHAR(10) CHARACTER SET UNICODE 1 7 2 male VARCHAR(10) CHARACTER SET UNICODE 0 10 2 female VARCHAR(10) CHARACTER SET UNICODE 1 16 2 female VARCHAR(10) CHARACTER SET UNICODE 1 21 2 male VARCHAR(10) CHARACTER SET UNICODE 0 40 2 female VARCHAR(10) CHARACTER SET UNICODE 1 61 2 male VARCHAR(10) CHARACTER SET UNICODE 0 1000 2 ? VARCHAR(10) CHARACTER SET UNICODE 1 2 3 C VARCHAR(10) CHARACTER SET LATIN 1 4 3 S VARCHAR(10) CHARACTER SET LATIN 1 7 3 S VARCHAR(10) CHARACTER SET LATIN 0 10 3 C VARCHAR(10) CHARACTER SET LATIN 1 16 3 S VARCHAR(10) CHARACTER SET LATIN 1 21 3 S VARCHAR(10) CHARACTER SET LATIN 0 40 3 C VARCHAR(10) CHARACTER SET LATIN 1 61 3 C VARCHAR(10) CHARACTER SET LATIN 0 1000 3 S VARCHAR(10) CHARACTER SET LATIN 1
SQL Call 4
SELECT * FROM TD_UNPIVOTING(
ON unpivoting_titanic_dataset AS InputTable PARTITION BY ANY
USING
IDCOLUMN('passenger')
TARGETCOLUMNS ('gender','fare')
ACCUMULATE ('survived')
INCLUDENULLS('true')
INPUTTYPES('true')
)AS dt ORDER BY 2, 1;
Output 4
passenger AttributeName AttributeValue_Num AttributeValue_Char survived --------- ------------- ------------------ ------------------- -------- 2 fare 7.12833000000000E 001 ? 1 4 fare 5.31000000000000E 001 ? 1 7 fare 5.18625000000000E 001 ? 0 10 fare 3.00708000000000E 001 ? 1 16 fare 1.60000000000000E 001 ? 1 21 fare 2.60000000000000E 001 ? 0 40 fare 1.12417000000000E 001 ? 1 61 fare 7.22920000000000E 000 ? 0 1000 fare 1.00500000000000E 002 ? 1 2 gender ? female 1 4 gender ? female 1 7 gender ? male 0 10 gender ? female 1 16 gender ? female 1 21 gender ? male 0 40 gender ? female 1 61 gender ? male 0 1000 gender ? ? 1
SQL Call 5
SELECT * FROM TD_UNPIVOTING(
ON unpivoting_titanic_dataset AS InputTable PARTITION BY ANY
USING
IDCOLUMN('passenger')
TARGETCOLUMNS ('gender','fare')
ATTRIBUTECOLNAME('Attribute')
VALUECOLNAME('value')
ACCUMULATE ('survived')
INCLUDENULLS('true')
INPUTTYPES('true')
)AS dt ORDER BY 2, 1;
Output 5
passenger Attribute Value_Num Value_Char survived --------- --------- --------- ---------- -------- 2 fare 7.12833000000000E 001 ? 1 4 fare 5.31000000000000E 001 ? 1 7 fare 5.18625000000000E 001 ? 0 10 fare 3.00708000000000E 001 ? 1 16 fare 1.60000000000000E 001 ? 1 21 fare 2.60000000000000E 001 ? 0 40 fare 1.12417000000000E 001 ? 1 61 fare 7.22920000000000E 000 ? 0 1000 fare 1.00500000000000E 002 ? 1 2 gender ? female 1 4 gender ? female 1 7 gender ? male 0 10 gender ? female 1 16 gender ? female 1 21 gender ? male 0 40 gender ? female 1 61 gender ? male 0 1000 gender ? ? 1