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