title: HIVE multiple rows to columns
body: <p>I have a file that I am processing and am trying to combine multiple rows with the same key down to columns in a single row.</p>
  
  <p>my input is </p>
  
  <pre><code>rental_company  field_name          field_data      location_identifier
  hertz           branchName          ANNABA AIRPORT  RCCTHERTZAAEREC000002
  hertz           branchCode          AAE             RCCTHERTZAAEREC000002
  hertz           countryCd           DZ              RCCTHERTZAAEREC000002
  hertz           isAtAirport         true            RCCTHERTZAAEREC000002
  hertz           branchPhone         0555066233      RCCTHERTZAAEREC000002
  hertz           branchHoursMon      08:00-20:00     RCCTHERTZAAEREC000002
  hertz           branchHoursTue      08:00-20:00     RCCTHERTZAAEREC000002
  hertz           branchHoursWed      08:00-20:00     RCCTHERTZAAEREC000002
  hertz           branchHoursThu      08:00-20:00     RCCTHERTZAAEREC000002
  hertz           branchHoursFri      closed          RCCTHERTZAAEREC000002
  hertz           branchHoursSat      closed          RCCTHERTZAAEREC000002
  hertz           branchHoursSun      08:00-20:00     RCCTHERTZAAEREC000002
  hertz           isParticipating     false           RCCTHERTZAAEREC000002
  hertz           airportCode         AAE             RCCTHERTZAAEREC000002
  hertz           shuttleServiceInd   false           RCCTHERTZAAEREC000002
  </code></pre>
  
  <p>and what I'd like to output is </p>
  
  <pre><code>location_identifier                 branchName          branchCode  branchPhone
  -----------------------------       ------------        ----------  --------------
  RCCTENTERPRISEE10101REC000002       LADUE RENTAL        E10101      (314) 863 6886
  </code></pre>
  
  <p>I have tired using the following:</p>
  
  <pre><code>SELECT
     location_identifier,
     kv('branchName') as branchName,
     kv('branchCode') as branchCode,
     kv('branchPhone') as branchPhone
  FROM (
     SELECT location_identifier, to_map(field_name, field_data) kv
     FROM vtable
     GROUP BY location_identifier
        ) t;
  </code></pre>
  
  <p>I end up with the following error: Expression not in GROUP BY key 'field_data' and when I add field_data I end up with the error that to_map is not recognized. I was trying to use the following example:
  <a href="https://docs.treasuredata.com/articles/sql-tips#use-of-pivot--unpivot" rel="nofollow noreferrer">https://docs.treasuredata.com/articles/sql-tips#use-of-pivot--unpivot</a></p>