title: How to declare new field values based on pivot value source field
body: <p>I will try to word this as best as possible, so please bear with me. I am unpivoting data from 15 fields in Greenplum PostgreSQL. These fields may or may not contain numbers that I am unnesting into the new field. If the field does contain a number I need to identify which field that value came from so that I may populate other fields based off of what field the data came from.</p>
  
  <p>Here is my query for better explanation:</p>
  
  <pre><code>SELECT geo_zone, customer___global,  field3, customer_type,     
  business_segment, product_business_line , product_brand, product_family,   
  license_type, license_status_sap, field11,
  
  unnest(array[ 
  field_2011_qtr_4_seat_qty_um ,
  field_2012_qtr_1_seat_qty_um ,
  field_2012_qtr_2_seat_qty_um ,
  field_2012_qtr_3_seat_qty_um ,
  field_2012_qtr_4_seat_qty_um ,
  field_2013_qtr_1_seat_qty_um ,
  field_2013_qtr_2_seat_qty_um ,
  field_2013_qtr_3_seat_qty_um ,
  field_2013_qtr_4_seat_qty_um ,
  field_2014_qtr_1_seat_qty_um ,
  field_2014_qtr_2_seat_qty_um ,
  field_2014_qtr_3_seat_qty_um ,
  field_2014_qtr_4_seat_qty_um ,
  field_2015_qtr_1_seat_qty_um ,
  field_2015_qtr_2_seat_qty_um ,
  field_2015_qtr_3_seat_qty_um ]) as seat_qty_um
  
  FROM table
  </code></pre>
  
  <p>From this query above I need to create 2 more fields in the result: YEAR and PERIOD. If the value for seat_qty_um came from the first field in the array, field_2011_qtr_4_seat_qty_um, I would need the YEAR field populated with 2011 and PERIOD populated with 4.</p>
  
  <p>If the value for seat_qty_um came from the second field in the array, field_2012_qtr_1_seat_qty_um, I would need the YEAR field populated with 2012 and PERIOD populated with 1 and so on.</p>
  
  <p>I haven't been able to come up with anything in my search. Is there a best way to implement this?</p>