Reference Columns in diffrent table in from cell values and merge resulst into one column

Question

I have a table (Area_Data) like this...

Area1         Area2         Area3
9118890       9228834       9338890

Area1,2,3 are column headers (I simplified Area_Data table for sake of this discussion but it has 12 area columns)

Then I have another table(Area_Offset) like this...

Name    Offset  Zone      
P5.3    -4      Area1
P3.3    -4      Area1
P3.4    -4      Area1
P5.4    -4      Area2
P61.1   -9      Area3
P5.1    -4      Area2
P3.1    -4      Area3

I'm trying to create a table like

Name    Offset  Zone      CalculatedOffset    Area
P5.3    -4      Area1    9118886             9118890       
P3.3    -4      Area1    9118886             9118890       
P3.4    -4      Area1    9118886             9118890       
P5.4    -4      Area2    9228830             9228834       
P61.1   -9      Area3    9338886             9338890
P5.1    -4      Area2    9228830             9228834       
P3.1    -4      Area3    9338886             9338890

(Calculated offset is not important I think I know how to create that column if I can get the relevant Area data into this table)

What should Should I be doing to achieve this? I tried google led me to some resources mentioning Pivot I attempted this but it didn't turn out right

what i tried was...

SELECT *

  FROM Area_Offset,
        Area_Data
  PIVOT
  (
    MAX(Area1)
    FOR
        Area1 IN (Zone)
  ) AS p

Then was this discussion TSQL Pivot without aggregate function but I couldn't get it too work.

Answer 1

You are looking for unpivot not pivot. I like to do this using outer apply:

select ao.*, (v.area + ao.offset) as CalculatedOffset, v.zone as area
from area_data ad outer apply
     (values (area1, 'area1'), (area2, 'area2'), (area3, 'area3')
     ) v(area, zone) join
     area_offset ao
     on ao.zone = v.zone;