Selecting values from another table based on columns stored as value on the table

Question

Table 1st :

---------------------------------------
    Column_A                Column_B
---------------------------------------
    Test A                  name
    Test B                  address
    Test C                  phone

Table 2nd :

-------------------------------------------------------------------
    name        address         email       country         phone           
-------------------------------------------------------------------
    Kush        KTM             a@a.c          NP           98545

2nd table is a temporary table and will hold single row only.

Output required :

---------------------------------------
    Column_A                Val
---------------------------------------
    Test A                  Kush
    Test B                  KTM
    Test C                  98545

I tried pivoting 2nd table, but since its gonna be table with dynamic number of columns, it will be complicated.

Is there any other alternative ?

Answer 1

The simple case with a fixed number of columns in Table2nd can be addressed with an unpivot:

SELECT a.Column_A, b.colVal
FROM
Table1st a
INNER JOIN
(
    SELECT 
        *
    FROM 
        Table2nd
    unpivot (
     colVal
     for Col in (name, address, email, country, phone)
    ) unpvt
) b
ON a.Column_B = b.col;

Fiddle here

The general case where you do not have fixed columns for Table2nd will need to be addressed via dynamic sql, but with the same unpivot. Have a look at bluefeet's answer here for how to do this.

You can get the dynamic columns for Table2nd in a couple of ways, e.g. via sys.columns, or if you assume that the columns specified in Table1st always exist, then from Table1st:

DECLARE @cols NVARCHAR(100);
SET @cols = STUFF((SELECT distinct ',' + Column_B 
            FROM Table1st
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

A couple of caveats:

• In order for the unpivot to work, the TYPES of the columns unpivoted will all need to be the same. Try casting through NVARCHAR if not the case.
• If you need to use QUOTENAME, you'll need to do this on both sides of the join.

Answer 2

This does not make sense!

You need to create a relationship between table A and B on either a single value PK (Primary Key) or multiple values (Composite Key). Table 1 will have a PK to FK (foreign key).

Column_A                Column_B 
---------------------------------------
Test A                  name
Test B                  address
Test C                  phone
Test D                  name


name        address         email       country         phone           
-------------------------------------------------------------------
Kush        KTM             a@a.c          NP           98545
Bush        BTM             b@b.d          NP           98545

What is the output now when both A and B have name as a value?

You are basically breaking the laws of Normality.

See below, databases usually are in 3rd normal form.

http://en.wikipedia.org/wiki/Database_normalization#Normal_forms

A better solution is the following.

Table 1

Test       Description      Surrogate 
---------------------------------------
Test A     name             1
Test B     address          2
Test C     phone            3
Test D     name             4

Table 2

Surrogate    Value
---------------------------------------
1            Kush
2            Bush

Use a surrogate key in table 1 (test/description) to relate to (values) in table 2.

http://en.wikipedia.org/wiki/Surrogate_key

There are other ways to do this, but without an written business rules, this is one way.

Answer 3

You can try a dynamic sql just like:

declare @query varchar(500) = (select stuff(
(SELECT ',' + B.NAME
from sys.tables A
inner join syscolumns B
    on A.[object_id] = B.id
where A.name = 'Table2'
FOR XML PATH('')),1,1,'') A)

set @query = 'select Column_A, Value from Table2 unpivot (Value for V in (' + @query + ')) B    
inner join Table1 on Table1.Column_B = B.V'

exec sp_sqlexec @query