Transposing syms to dynamic columns

[hoffmanroni]

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);

`a_val1`a_val2`b_val1`b_val2`c_val1`c_val2

//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

Thanks!

[TerryLynch]

The general pivot function on the Kx site will do this for you: https://code.kx.com/q/kb/pivoting-tables/ 

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. 

Terry

[hoffmanroni]

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