//// Pivot

syms=`BIDU`MSFT`ORCL$symbol
sym=syms[0 0 0 0 0 0 0 1 1 1 1 1 1 1 2 2 2 2 2 2 2]
price=172.12 170.32 172.25 172.55 175.1 174.85 174.5 36.45 36.15 36.3 35.9 36.5 37.15 36.9 40.1 40.2 40.25 40.15 40.1 40.05 39.95
qty=100* 10 3 7 8 25 6 10 4 5 1 2 8 6 10 2 2 5 5 4 4 3
trade_time=09:40:00+1 30 65 90 130 185 195 10 40 90 140 160 190 200 5 45 80 140 170 190 210
t1=table(sym, price, qty, trade_time);
t1;

//count the number of observations within each minute for each stock:
pivot(count, price, minute(trade_time), sym);

//display the last observation within each minute for each stock:
pivot(last, price, minute(trade_time), sym);

//we can also use the SQL clause pivot by for the example above:
select last(price) from t1 pivot by minute(trade_time), sym;

//unpivot
t=table(1..3 as id, 4..6 as col1, 7..9 as col2);
t.unpivot(`id, `col1`col2);

//calculate average pairwise correlation of daily stock returns for 4 stocks with 5 days of observations with the template function pivot:
x=2017.03.27..2017.03.31
date=take(x,20)
symbol=take(`A`B`C`D, 20).sort()
returns=0.0121 0.0059 -0.0215 -0.004 0.0312 0.0019 0.0004 -0.0321 -0.0098 -0.0001 0.0003 0.0078 -0.0276 -0.0021 0.0219 0.0087 -0.0003 -0.0127 -0.0022 0.0186
t = table(date, symbol, returns);
t;

retMat = pivot(last, t.returns, t.date, t.symbol);
corrs = retMat corr :C retMat;
rfor(i in 0:ows(corrs)) corrs[i,i]=NULL;
corrs.flatten().avg();