title: pig - transform data from rows to columns while inserting placeholders for non-existent fields in specific rows
body: <p>Suppose I have the following flat file on HDFS (let's call this key_value):</p>
  
  <pre><code>1,1,Name,Jack
  1,1,Title,Junior Accountant
  1,1,Department,Finance
  1,1,Supervisor,John
  2,1,Title,Vice President
  2,1,Name,Ron
  2,1,Department,Billing
  </code></pre>
  
  <p>Here is the output I'm looking for:</p>
  
  <pre><code>(1,1,Department,Finance,Name,Jack,Supervisor,John,Title,Junior Accountant)
  (2,1,Department,Billing,Name,Ron,,,Title,Vice President)
  </code></pre>
  
  <p>In other words, the first two columns form a unique identifier (similar to a composite key in db terminology) and for a given value of this identifier, we want one row in the output (i.e., the last two columns - which are effectively key-value pairs - are condensed onto the same row as long as the identifier is the same). Also notice the nulls in the second row to add placeholders for Supervisor piece that's missing when the unique identifier is (2, 1).</p>
  
  <p>Towards this end, I started putting together this pig script:</p>
  
  <pre><code>data = LOAD 'key_value' USING PigStorage(',') as (i1:int, i2:int, key:chararray, value:chararray);
  data_group = GROUP data by (i1, i2);
  expected = FOREACH data_group {
     sorted = ORDER data BY key, value;
     GENERATE FLATTEN(BagToTuple(sorted));
  };
  dump expected;
  </code></pre>
  
  <p>The above script gives me the following output:</p>
  
  <pre><code>(1,1,Department,Finance,1,1,Name,Jack,1,1,Supervisor,John,1,1,Title,Junior Accountant)
  (2,1,Department,Billing,2,1,Name,Ron,2,1,Title,Vice President)
  </code></pre>
  
  <p>Notice that the null place holders for missing Supervisor are not represented in the second record (which is expected). If I can get those nulls into place, then it seems just a matter of another projection to get rid of redundant columns (the first two which are replicated multiple times - once per every key value pair). </p>
  
  <p>Short of using a UDF, is there a way to accomplish this in pig using the in-built functions?</p>
  
  <p><strong>UPDATE:</strong> As WinnieNicklaus correctly pointed out, the names in the output are redundant. So the output can be condensed to:</p>
  
  <pre><code>(1,1,Finance,Jack,John,Junior Accountant)
  (2,1,Billing,Ron,,Vice President)
  </code></pre>