title: hive convert columns into a row
body: <p>Here is the data i have in hive:</p>
  
  <p><strong>customers</strong></p>
  
  <pre><code> id |  name
   ---+-------
   1  |  n1
   2  |  n2
  </code></pre>
  
  <p><strong>orders</strong></p>
  
  <pre><code>oid | cid | amt
  ----+-----+----
   1  |  1  | 10
   2  |  1  | 20
   3  |  1  | 30
   4  |  2  | 10
  </code></pre>
  
  <p>I would like to get something like this:</p>
  
  <p>cid, avg(amt), oid1,oid2,oid3...etc</p>
  
  <p>in other words i want each <code>custid</code>, avg of amt and all the order ids associated with <code>cid</code> in 1 row.</p>
  
  <p>I have come up with something like this:</p>
  
  <pre><code>select c.id,avg(o.amt),.... from customers c join orders o on c.id = o.cid;
  </code></pre>
  
  <p>can some one please fill up how to achieve this please.</p>