Create one record with multiple using field names from data values

1

I’m looking for an easy way to create one record from many records. Along with this as the one record is created, the field names need to come from the values in the multiple records. Here’s what I have…
WHAT I HAVE
Field1 Field2 Field3 Field4 Month VALUEa VALUEb VALUEc
Field1 f2a f3a f4a APR $101.00 $10.00 $111.00
Field1 f2a f3a f4a APR $102.00 $11.00 $113.00
Field1 f2a f3a f4b APR $103.00 $12.00 $115.00
Field1 f2a f3b f4b APR $104.00 $13.00 $117.00
Field1 f2a f3b f4c APR $105.00 $14.00 $119.00
Field1 f2b f3b f4c APR $106.00 $15.00 $121.00
Field1 f2b f3c f4c APR $107.00 $16.00 $123.00
Field1 f2b f3c f4d APR $108.00 $17.00 $125.00
Field1 f2b f3c f4d APR $109.00 $18.00 $127.00
Field1 f2b f3c f4e APR $110.00 $19.00 $129.00

Field1 f2a f3a f4a MAY $101.00 $10.00 $111.00
Field1 f2a f3a f4a MAY $102.00 $11.00 $113.00
Field1 f2a f3a f4b MAY $103.00 $12.00 $115.00
Field1 f2a f3b f4b MAY $104.00 $13.00 $117.00
Field1 f2a f3b f4c MAY $105.00 $14.00 $119.00
Field1 f2b f3b f4c MAY $106.00 $15.00 $121.00
Field1 f2b f3c f4c MAY $107.00 $16.00 $123.00
Field1 f2b f3c f4d MAY $108.00 $17.00 $125.00
Field1 f2b f3c f4d MAY $109.00 $18.00 $127.00
Field1 f2b f3c f4e MAY $110.00 $19.00 $129.00

...and here's what I need...
WHAT I NEED
Field1 Field2 Field3 Field4 APR_VALUEa APR_VALUEb APR_VALUEc MAY_VALUEa MAY_VALUEb MAY_VALUEc APR_MAY_CombinedVALUEa APR_MAY_CombinedVALUEb APR_MAY_CombinedVALUEc
Field1 f2a f3a f4a $203.00 $21.00 $224.00 $203.00 $21.00 $224.00 $406.00 $42.00 $448.00
Field1 f2a f3a f4b $207.00 $25.00 $232.00 $207.00 $25.00 $232.00 $414.00 $50.00 $464.00
Field1 f2b f3c f4c $318.00 $45.00 $363.00 $318.00 $45.00 $363.00 $636.00 $90.00 $726.00
Field1 f2b f3c f4d $217.00 $35.00 $252.00 $217.00 $35.00 $252.00 $434.00 $70.00 $504.00

(sorry, didn't copy over very well)

2

select Field1, Field2, Field3, Field4,
sum(case when Month='APR' then VALUEa else 0 end) APR_VALUEa,
sum(case when Month='APR' then VALUEb else 0 end) APR_VALUEb,
sum(case when Month='APR' then VALUEc else 0 end) APR_VALUEc,
sum(case when Month='MAY' then VALUEa else 0 end) MAY_VALUEa,
sum(case when Month='MAY' then VALUEb else 0 end) MAY_VALUEb,
sum(case when Month='MAY' then VALUEc else 0 end) MAY_VALUEc,
sum(VALUEa) APR_MAY_CombinedVALUEa,
sum(VALUEb) APR_MAY_CombinedVALUEb,
sum(VALUEc) APR_MAY_CombinedVALUEc
from tbldata
group by Field1, Field2, Field3, Field4

3

Is there an easier way to do this ~ more dynamic is what I'm looking for. This is going to be for the entire year, so I'll have 12 months * each field, I was just looking for a way that could look at my VALUE fields and automatically add the field name and to it. This will allow for less hardcoding, which is what I'm looking for as this table is expected to grow with more fields based on the values in various fields.

4

Then write the same query logic in dynamic query.