0

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.

Here is my query for better explanation:

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

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.

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.

I haven't been able to come up with anything in my search. Is there a best way to implement this?

user3329160
  • 165
  • 2
  • 13

1 Answers1

1

Provided I understood your question right, I would suggest using "case when". Like this:

SELECT geo_zone, customer___global,  field3, customer_type,     
business_segment, product_business_line , product_brand, product_family,   
license_type, license_status_sap, field11,

case when field_2011_qtr_4_seat_qty_um is not null then 2011
    when field_2012_qtr_1_seat_qty_um is not null then 2012
    when field_2012_qtr_2_seat_qty_um is not null then 2012
    when field_2012_qtr_3_seat_qty_um is not null then 2012
    end as YEAR,

case when field_2011_qtr_4_seat_qty_um is not null then 4
    when field_2012_qtr_1_seat_qty_um is not null then 1
    when field_2012_qtr_2_seat_qty_um is not null then 2
    when field_2012_qtr_3_seat_qty_um is not null then 3
    end as PERIOD,

FROM table

That's it.

Ana
  • 26
  • 1
  • 5
  • I thought about this, but just using a case statement is not going to guarantee that the correct value that correlates with the seat_qty_um field is used. – user3329160 Nov 24 '15 at 18:50
  • Well, maybe I didn't understand well enough your question. What do you understand by "correct value for seat_qty_um" ? Is it this: when for example "field_2012_qtr_3_seat_qty_um" has a number, then seat_qty_um = field_2012_qtr_3_seat_qty_um ? – Ana Nov 25 '15 at 13:17