Converting KDB Table to dictionary in special format

Question

I am quiet new to KDB/Q , I have table having two columns only like below:

id      value
100       a
101       b
100       c
102       d
103       e
100       f
102       g
101       e

I need to convert this table in form of dictionary group by id like in this format

id  | x1   x2  x3  x4  x5  x6  x7  x8 
100 | a    ::  c   ::  ::   f  ::  ::
101 | ::   b  ::   ::  ::   :: ::  e
102 | ::  ::  ::   d   ::   ::  g  ::  
103 | ::  ::  ::   ::   e   f  ::  ::

I tried with by but its giving me only group by id not able convert like keyed table or dictionary

can someone help me in doing the same. Columns name (x1,x2 etc are not important).

Answer 1

Think maybe you're looking for an exec by from table

q)t:([]id:100 101 100 102 103 100 102 101;val:`a`b`c`d`e`f`g`e)
q)exec val by id from t
100| `a`c`f
101| `b`e
102| `d`g
103| ,`e
q)// if you need the (::)
q)exec {@[count[t]#(::);x;:;y]}[i;val] by id from t
100| `a :: `c :: :: `f :: ::
101| :: `b :: :: :: :: :: `e
102| :: :: :: `d :: :: `g ::
103| :: :: :: :: `e :: :: ::

Answer 2

It sounds like you want to pivot: https://code.kx.com/q/kb/pivoting-tables/

q)t:([]id:100 101 100 102 103 100 102 101;val:`a`b`c`d`e`f`g`e)
q)exec distinct[t`val]#val!val by id:id from t
id | a b c d e f g
---| -------------
100| a   c     f
101|   b     e
102|       d     g
103|         e