Example: How to Use TD_Unpivoting - Analytics Database

Database Analytic Functions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-06
dita:mapPath
gjn1627595495337.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
jmh1512506877710
Product Category
Teradata Vantageā„¢

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