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.

score 1 · Accepted Answer · answered Jun 21 '17 at 21:53

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;

answered Jun 21 '17 at 21:53

Gordon Linoff

1,242,037
58
646
786

Thanks for pointing me to unpivot, I managed to get it working with unpivot. I'm going to give it a try getting to get it working with outer apply later today – daeden Jun 24 '17 at 12:48

score 1 · Answer 2 · answered Jun 22 '17 at 13:24

1

Using Cross Apply also we can do

Select 
TT.Name,
TT.Offset,
TT.Zone,  
CONVERT(INT,T.VAL)  - CONVERT(INT,REPLACE(TT.Offset,'-',''))    ,T.VAL 
from  ( 
select COL,VAL 
from @Table1
    CROSS APPLY (VALUES ('Area1',Area1),
                        ('Area2',Area2),
                        ('Area3',Area3))CS(COL,VAL))T
                        INNER JOIN  @Table2 TT
                        ON T.COL = TT.Zone

answered Jun 22 '17 at 13:24

mohan111

8,633
4
28
55

@daeden please have a look – mohan111 Jun 22 '17 at 13:25

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

2 Answers2