Showing results for 
Search instead for 
Did you mean: 

Transposing syms to dynamic columns

New Contributor III
Hi All,

Wondering if anyone can help.  I'm sure theres a better way to do this.  I am trying to take a table with syms, transpose them to columns dynamically change the column names to include the sym.
I've done it this way but it seems a bit brute force and I'm sure theres better ways to do this.  

//gen table
q)t:flip (`sym`val1`val2)!(`a`b`c;(1f;2f;3f);(4f;5f;6f));

sym val1 val2
a   1    4
b   2    5
c   3    6

//gen all new col names        
q)newcols:raze {`$(raze string[x[0],"_"]),/:string[x[1]]} each (exec sym from t),\: enlist ((cols t) except `sym);


//update table t to includes all new cols
t:eval (!;t;();0b;(newcols)!(count newcols)#0n);

sym val1 val2 a_val1 a_val2 b_val1 b_val2 c_val1 c_val2
a   1    4
b   2    5
c   3    6

//iterate each sym and set updated cols
q)raze {
   t2:select from x[1] where sym=x[0];

   eval (!;t2;();0b;((`$string[t2[0][`sym]],"_val1");(`$string[t2[0][`sym]],"_val2"))!(t2[0][`val1];t2[0][`val2]))
 }  each ((exec sym from t),\:enlist t)

sym val1 val2 a_val1 a_val2 b_val1 b_val2 c_val1 c_val2
a 1 4 1 4 0n 0n 0n 0n
b 2 5 0n 0n 2 5 0n 0n
c 3 6 0n 0n 0n 0n 3 6


New Contributor II
The general pivot function on the Kx site will do this for you: 

q)piv[t;1#`sym;1#`sym;`val1`val2;{`$"_"sv'string raze y,\:/:x};{x,z}]
sym| a_val1 b_val1 c_val1 a_val2 b_val2 c_val2
---| -----------------------------------------
a  | 1                    4
b  |        2                    5
c  |               3                    6

Use the final function {x,z} to reorder the columns if you want the "a"'s together etc, and you can join on your original columns with a left join. 


Thanks very much for this Terry, and pointing to wiki article, much better.