# pivot table

**URL:** https://forum.kx.com/t/pivot-table/11459
**Category:** Community Support
**Tags:** kdb-and-q
**Created:** [January 23, 2017, 5:57am UTC](https://forum.kx.com/t/pivot-table/11459 "2017-01-23T05:57:00Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![James11](https://avatars.discourse-cdn.com/v4/letter/j/e5b9ba/32.png) [@James11](https://forum.kx.com/u/James11)
#### Post date: [January 23, 2017, 5:57am UTC](https://forum.kx.com/t/pivot-table/11459/1 "2017-01-23T05:57:00Z")

</div>

Hi,\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

**1.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;**** Define table t\<o:p\>\</o:p\>**

t:(id:1 1 1 2 2 2;c1:`a`a`b`a`a`b;c2:`x1`x2`x3`y1`y2`y3)\<o:p\>\</o:p\>

id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c1&nbsp;&nbsp;&nbsp;&nbsp; c2\<o:p\>\</o:p\>

-----------------\<o:p\>\</o:p\>

1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x1\<o:p\>\</o:p\>

1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x2\<o:p\>\</o:p\>

1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; b&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x3\<o:p\>\</o:p\>

2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y1\<o:p\>\</o:p\>

2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y2\<o:p\>\</o:p\>

2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; b&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y3\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

**2.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;**** Pivot table t by this function:\<o:p\>\</o:p\>**

pivot:{P:asc exec distinct c1 from t;:exec P#(c1!c2) by id:id from t}\<o:p\>\</o:p\>

pivot\<o:p\>\</o:p\>

id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; b\<o:p\>\</o:p\>

-------------------\<o:p\>\</o:p\>

1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x1&nbsp;&nbsp;&nbsp;&nbsp; x3\<o:p\>\</o:p\>

2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y1&nbsp;&nbsp;&nbsp;&nbsp; y3\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

\<o:p\> \</o:p\>

**However, I want to get the pivot table like this:\<o:p\>\</o:p\>**

id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; b\<o:p\>\</o:p\>

-------------------\<o:p\>\</o:p\>

1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x1,x2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x3\<o:p\>\</o:p\>

2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y1,y2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y3\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

\<o:p\> \</o:p\>

How to fix it?\<o:p\>\</o:p\>

---

<div class="post-metadata">

### Author: ![robsmith11](https://avatars.discourse-cdn.com/v4/letter/r/90db22/32.png) [@robsmith11](https://forum.kx.com/u/robsmith11)
#### Post date: [January 23, 2017, 6:30am UTC](https://forum.kx.com/t/pivot-table/11459/2 "2017-01-23T06:30:00Z")

</div>

exec distinct[c1]#c2 group c1 by id:id from tyou may want to add a “distinct each” if you want only unique c2 values.On 23 January 2017 05:57 UTC, Roy Liu wrote:\> Hi,\>\> \>\> 1. Define table t\>\> t:(id:1 1 1 2 2 2;c1:`a`a`b`a`a`b;c2:`x1`x2`x3`y1`y2`y3)\>\> id c1 c2\>\> -----------------\>\> 1 a x1\>\> 1 a x2\>\> 1 b x3\>\> 2 a y1\>\> 2 a y2\>\> 2 b y3\>\> \>\> 2. Pivot table t by this function:\>\> pivot:{P:asc exec distinct c1 from t;:exec P#(c1!c2) by id:id from t}\>\> pivot\>\> id a b\>\> -------------------\>\> 1 x1 x3\>\> 2 y1 y3\>\> \>\> \>\> However, I want to get the pivot table like this:\>\> id a b\>\> -------------------\>\> 1 x1,x2 x3\>\> 2 y1,y2 y3\>\> \>\> \>\> How to fix it?

---

<div class="post-metadata">

### Author: ![rahul\_asati041](https://avatars.discourse-cdn.com/v4/letter/r/b5e925/32.png) [@rahul\_asati041](https://forum.kx.com/u/rahul_asati041)
#### Post date: [January 23, 2017, 10:27am UTC](https://forum.kx.com/t/pivot-table/11459/3 "2017-01-23T10:27:00Z")

</div>

Another option:

pivot:{exec c2 group c1 by id:id from t}

---

<div class="post-metadata">

### Author: ![James11](https://avatars.discourse-cdn.com/v4/letter/j/e5b9ba/32.png) [@James11](https://forum.kx.com/u/James11)
#### Post date: [January 24, 2017, 3:13am UTC](https://forum.kx.com/t/pivot-table/11459/4 "2017-01-24T03:13:00Z")

</div>

It works!  
Thanks all.

* * *

Best Regards,

Roy

\< RAHUL ASATI\> 2017-01-23 18:27:33 wrote:

> Another option:
> 
> pivot:{exec c2 group c1 by id:id from t}
> 
> On Monday, 23 January 2017 05:58:02 UTC, Roy wrote:
> 
> > Hi,
> > 
> > &nbsp;
> > 
> > **1.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;**** Define table t**
> > 
> > t:(id:1 1 1 2 2 2;c1:`a`a`b`a`a`b;c2:`x1`x2`<wbr>x3`y1`y2`y3)
> > 
> > id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; c1&nbsp;&nbsp;&nbsp;&nbsp; c2
> > 
> > * * *
> > 
> > 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x1
> > 
> > 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x2
> > 
> > 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; b&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x3
> > 
> > 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y1
> > 
> > 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y2
> > 
> > 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; b&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y3
> > 
> > &nbsp;
> > 
> > **2.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;**** Pivot table t by this function:**
> > 
> > pivot:{P:asc exec distinct c1 from t;:exec P#(c1!c2) by id:id from t}
> > 
> > pivot
> > 
> > id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; b
> > 
> > * * *
> > 
> > 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x1&nbsp;&nbsp;&nbsp;&nbsp; x3
> > 
> > 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y1&nbsp;&nbsp;&nbsp;&nbsp; y3
> > 
> > &nbsp;
> > 
> > &nbsp;
> > 
> > **However, I want to get the pivot table like this:**
> > 
> > id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; b
> > 
> > * * *
> > 
> > 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x1,x2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; x3
> > 
> > 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y1,y2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y3
> > 
> > &nbsp;
> > 
> > &nbsp;
> > 
> > How to fix it?
> 
> –  
> Submitted via Google Groups