Aggregation with PivotColumn - 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ā„¢

The following example assumes the star1 table exists:

CREATE MULTISET TABLE star1(country VARCHAR(20),state VARCHAR(10), yr INTEGER, qtr VARCHAR(3), sales INTEGER, cogs INTEGER, rating varchar(10));
insert into star1 values('USA',    'CA', 2001  ,'Q1',  30,  15, 'A');
insert into star1 values('Canada', 'ON', 2001  ,'Q2',  10,   0, 'B');
insert into star1 values('Canada', 'BC', 2001  ,'Q3',  15,   0, 'A');
insert into star1 values('Canada', 'BC', 2001  ,'Q3',  10,   0, 'A');
insert into star1 values('USA',    'NY', 2001  ,'Q1',  45,  25, 'D');
insert into star1 values('USA',    'CA', 2001  ,'Q2',  50,  20, 'A');
insert into star1 values('USA',    'CA', 2001  ,'Q2',  5,  5, 'B');

Calling TD_Pivoting with Aggregation with PivotColumn

SELECT * FROM TD_Pivoting (
  ON star1 AS InputTable PARTITION BY country, state Order By qtr
  USING
  PartitionColumns ('country', 'state')
  TargetColumns ('sales', 'cogs', 'rating')
  Accumulate('yr')
  PivotColumn('qtr')
  PivotKeys('Q1','Q2','Q3')
  Aggregation('sales:SUM','cogs:AVG','rating:CONCAT')
  Delimiters('|')
  CombinedColumnSizes(64001)
) AS dt Order By country;

TD_Pivoting Output

country state sales_Q1 sales_Q2 sales_Q3 cogs_Q1 cogs_Q2 cogs_Q3 rating_Q1 rating_Q2 rating_Q3 yr
------- ----- -------- -------- -------- ------- ------- ------- --------- --------- --------- --
Canada  BC    ?         ?       25        ?       ?      0.000   ?         ?         A|A       2001
Canada  ON    ?        10        ?        ?       0.000  ?       ?         B         ?         2001
USA     CA    30       55        ?       15.000  12.500  ?       A         B|A       ?         2001
USA     NY    45        ?        ?       25.000   ?      ?       D         ?         ?         2001