Search code examples
kdb

How to pivot partitioned table

Since you can't use exec on partitioned tables, how can they be pivoted?

Code I used to pivot in-memory table as seen on kx https://code.kx.com/q/kb/pivoting-tables/

exec P#(p!v) by k:k from t 

k   v
1   10  0N  0N
2   40  20  0N
3   0N  50  30

This is what I want ^.

But since I have to use select on partitioned tables, the output I get from select P#(p!v) by k:k from t 

k    v
1   `s#`xx`yy`zz!10 0N 0N
2   `s#`xx`yy`zz!40 20 0N
3   `s#`xx`yy`zz!0N 50 30

How can I convert this 2nd result into the first result without using exec initially/loading the whole table into memory? Also since symbols are enumerated, do they have to be converted to be columns?

Solution

  • You can exec after a select, and yes you should unenumerate.

    P:value exec distinct p from select p from pivotTab where date=...
exec P#(p!v) by k:k from select k,p,v from pivotTab where date=...

    To clarify - your P should be unenumerated