Using the names of columns stored within fields to retrieve data from a different table

Question

I was wondering if there exists code to accomplish the following in SQL-Server 2008?

Table 1:

id    column name
-------------------
1     col1
2     col2
3     col3
4     col2

Table 2:

col1    col2    col3
--------------------
a       b       c

Result Table:

id    data
--------------------
1     a
2     b
3     c
4     b

Thanks in advance, I really have no idea how to do this.

I'm not entirely sure what you're going for so I"ll just throw this out there: look at `pivot`. If you want to do things dynamically, though, you might have to use some dynamic sql ([ref](http://stackoverflow.com/questions/2170058/can-sql-server-pivot-without-knowing-the-resulting-column-names)) — Michael Haren, Sep 21 '12 at 16:02

Taryn · Accepted Answer · 2012-09-21T16:12:59.067

2

You can use UNPIVOT table2 to access the data from the columns:

select t1.id, t2.value
from table1 t1
left join 
(
  select value, col
  from table2
  unpivot
  (
    value
    for col in (col1, col2, col3)
  ) u
) t2
  on t1.name = t2.col

see SQL Fiddle with Demo

Or you can use a UNION ALL to access the data in table2:

select t1.id, t2.value
from table1 t1
left join 
(
  select col1 value, 'col1' col
  from table2
  union all
  select col2 value, 'col2' col
  from table2
  union all
  select col3 value, 'col3' col
  from table2
) t2
  on t1.name = t2.col

see SQL Fiddle with Demo

edited Sep 21 '12 at 16:12

answered Sep 21 '12 at 16:07

Taryn

242,637
56
362
405

Thank you very much! Both examples are so useful when I didn't have a clue how to approach this problem. – user1689283 Sep 23 '12 at 10:30

score 0 · Answer 2 · answered Sep 21 '12 at 16:05

0

I dont see how you do it withou a column connection them:

Table1:
ID
ColumnName

Table2:
Table1ID
Letter


Select table1.id, table2.Letter 
from table1 
inner join table2 on table1.ID = table2.Table1ID

answered Sep 21 '12 at 16:05

RollRoll

8,133
20
76
135

score 0 · Answer 3 · answered Sep 21 '12 at 16:09

You can do this with a case statement and cross join:

select t1.id,
       (case when t1.columnname = 'col1' then t2.col1
             when t1.columnname = 'col2' then t2.col2
             when t1.columnname = 'col3' then t2.col3
        end) as data
from table1 t1 cross join
     table2 t2

Using the names of columns stored within fields to retrieve data from a different table

3 Answers3

see SQL Fiddle with Demo

see SQL Fiddle with Demo