CODEWITHSUNDEEP
kdb+
JejeBelfort
JejeBelfort

Reputation: 1663

Pivot table with multiple keyed columns

I have the following table:

t:(([]y:2001 2002) cross ([]m:5 6 7) cross ([]sector:`running`hiking`swimming`cycling)),'([]sales: 14 12 5 9 4 894 1 4 87 12 24 6 4 8 64 354 3 4 86 43 1053 2 43 4);

y    m    sector   sales
------------------------
2001 5    running  14   
2001 5    hiking   12   
2001 5    swimming 5    
2001 5    cycling  9    
2001 6    running  4    
2001 6    hiking   894  
2001 6    swimming 1    
2001 6    cycling  4    
...    
2002 5    running  4    
2002 5    hiking   8    
2002 5    swimming 64   
2002 5    cycling  354  
2002 6    running  3    
...

I want to pivot the sales values by sector, while keeping the first two y and m columns, such that the resulting table would look like this:

y    m   cycling hiking running swimming
--------------------------------------
2001 5   9       12     14      5       
2001 6   4       894    4       1       
2001 7   6       12     87      24      
2002 5   354     8      4       64      
2002 6   43      4      3       86      
2002 7   4       2      1053    43

Upvotes: 1

Views: 239

Answers (1)

Ferenc Bodon
Ferenc Bodon

Reputation: 452

As per https://code.kx.com/v2/kb/pivoting-tables/

q) P:asc exec distinct sector from t;
q) exec P#(sector!sales) by y:y,m:m from t

You can unkey the result by () xkey if you need a normal table.

Upvotes: 2

Related Questions