pivot by

Example 1

sym = `C`MS`MS`MS`IBM`IBM`C`C`C
price= 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29
qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800
timestamp = [09:34:07,09:35:42,09:36:51,09:36:59,09:35:47,09:36:26,09:34:16,09:35:26,09:36:12]
t2 = table(timestamp, sym, qty, price);

t2;

select price from t2 pivot by timestamp, sym;

a=select last(price) from t2 pivot by timestamp.minute(), sym;

a;

typestr a;
// output
TABLE

b=exec count(price) from t2 pivot by timestamp.minute(), sym;
b;

typestr b;
// output
FAST DOUBLE MATRIX

Example 2. Calculate the basket value of an ETF's constituents. For simplicity, assume an ETF has 2 constituents AAPL and FB.

symbol=take(`AAPL, 6) join take(`FB, 5)
time=2019.02.27T09:45:01.000000000+[146, 278, 412, 445, 496, 789, 212, 556, 598, 712, 989]
price=173.27 173.26 173.24 173.25 173.26 173.27 161.51 161.50 161.49 161.50 161.51
quotes=table(symbol, time, price)
weights=dict(`AAPL`FB, 0.6 0.4)
ETF = select symbol, time, price*weights[symbol] as price from quotes;

select price from ETF pivot by time, symbol;

To calculate the basket value of this ETF's constituents at each timestamp that any of its constituents had a trade, we need to forward fill each stock's price in the pivot table, and then sum each row's weighted price. They can be completed in just one SQL statement with "pivot by".

select rowSum(ffill(price)) from ETF pivot by time, symbol;

Example 3. The names of the newly created columns from a pivot by clause will be automatically adjusted.

Before version 1.30.14, column names in DolphinDB can only use letters, digits or underscores (_), and must start with letters.

Since version 1.30.14, column names generated by pivot by, addColumn can contain special characters or start with digits.

For details, please refer to section Creating tables in Table.

date = take(2021.08.01  2021.08.02 2021.08.03, 12)
sym = take(["IBM N", "_MSFTN", "3_GOOGS", ""], 12).sort()
value = 1..12
t=table(date, sym, value)
re = select value from t pivot by date, sym

select  _"NULL" from re

select  _"3_GOOGS" from re

In the following example, set removeSpecialCharInColumnName = true. The stock symbols "600300" and "600600" are adjusted to "c600300" and "c600600" as column names.

symbol=take(`600300, 6) join take(`600600, 5)
time=2019.02.27T09:45:01.000000000+[146, 278, 412, 445, 496, 789, 212, 556, 598, 712, 989]
price=173.27 173.26 173.24 173.25 173.26 173.27 161.51 161.50 161.49 161.50 161.51
quotes=table(symbol, time, price)
weights=dict(`600300`600600, 0.6 0.4)
ETF = select symbol, time, price*weights[symbol] as price from quotes;

select last(price) from ETF pivot by time, symbol;

Example 4. The pivot by clause includes more than 2 columns.

date = 2020.09.21 + 0 0 0 0 1 1 1 1
sym = `MS`MS`GS`GS`MS`MS`GS`GS$SYMBOL
factorNum = 1 2 1 2 1 2 1 2
factorValue = 1.2 -3.4 -2.5 6.3 1.1 -3.2 -2.1 5.6
t = table(date, sym, factorNum, factorValue);

t;

select factorValue from t pivot by date, sym, factorNum;

Select multiple columns for each factorNum

select factorValue, factorValue>0 as factorSign from t pivot by date, sym, factorNum;