title: PIG query to pivot rows and columns with count of like rows
body: <p>Trying to create sql or PIG queries that will yield a count of distinct values results based on type.</p>
  
  <p>In other words, given this table:</p>
  
  <pre><code>Type:    Value:
  A        x
  B        y
  C        y
  B        y
  C        z
  A        x
  A        z
  A        z
  A        x
  B        x
  B        z
  B        x
  C        x
  </code></pre>
  
  <p>I want to get the following results:</p>
  
  <pre><code>Type:    x:    y:    z:
  A         3     0     2
  B         2     2     1
  C         1     1     1
  </code></pre>
  
  <p>Additionally, a table of averages as a result would be helpful too</p>
  
  <pre><code>Type:    x:    y:    z:
  A         0.60  0.00  0.40
  B         0.40  0.40  0.20 
  C         0.33  0.33  0.33
  </code></pre>
  
  <p><strong>EDIT 4</strong></p>
  
  <p>I am a nooby at PIG, but reading 8 different stack overflow posts I came up with this.</p>
  
  <p>When I use this PIG query</p>
  
  <pre><code>A = LOAD 'tablex' USING org.apache.hcatalog.pig.HCatLoader();
  x = foreach A GENERATE id_orig_h;
  xx = distinct x;
  y = foreach A GENERATE id_resp_h;
  yy = distinct y;
  yyy = group yy all;
  zz = GROUP A BY (id_orig_h, id_resp_h);
  B = CROSS xx, yy;
  C = foreach B generate xx::id_orig_h as id_orig_h, yy::id_resp_h as id_resp_h;
  D = foreach zz GENERATE flatten (group) as (id_orig_h, id_resp_h), COUNT(A) as count;
  E = JOIN C by (id_orig_h, id_resp_h) LEFT OUTER, D BY (id_orig_h, id_resp_h);
  F = foreach E generate C::id_orig_h as id_orig_h, C::id_resp_h as id_resp_h, D::count as count;
  G = foreach yyy generate 0 as id:chararray, flatten(BagToTuple(yy));
  H = group F by id_orig_h;
  I = foreach H generate group as id_orig_h, flatten(BagToTuple(F.count)) as count;
  dump G;
  dump I;
  </code></pre>
  
  <p>Sort of works.......</p>
  
  <p>I get this:</p>
  
  <pre><code>(0,x,y,z)
  (A,3,0,2)
  (B,2,2,1)
  (C,1,1,1)
  </code></pre>
  
  <p>Which I can import to a text file strip the "(" and ")" and use as a CSV with schema being first line.  This sort of works it is <strong>SO SLOW</strong>. I would like a nicer, faster, cleaner way of doing this.  If anyone out there knows of a way please let me know.</p>