3  

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

Table2nd中具有固定列数的简单情况可以通过以下方式解决:

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.

您没有Table2nd固定列的一般情况需要通过动态sql解决,但使用相同的unpivot。看看bluefeet在这里的答案如何做到这一点。

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:

您可以通过几种方式获取Table2nd的动态列,例如通过sys.columns,或者假设Table1st中指定的列始终存在,那么从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.

为了使unpivot工作,未透露的列的类型都需要相同。如果不是这样的话,尝试通过NVARCHAR进行投射。

• If you need to use QUOTENAME, you'll need to do this on both sides of the join.

如果您需要使用QUOTENAME,则需要在连接的两侧执行此操作。

0  

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).

您需要在单个值PK(主键)或多个值(复合键)上创建表A和B之间的关系。表1将具有PK到FK(外键)。

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?

当A和B都有名称作为值时,输出现在是什么?

You are basically breaking the laws of Normality.

你基本上违反了常态法则。

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

见下文,数据库通常是第3范式。

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.

使用表1中的代理键(测试/描述)与表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.

还有其他方法可以做到这一点,但没有书面的业务规则,这是一种方式。

0  

You can try a dynamic sql just like:

您可以尝试动态sql,如:

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