根据存储为表中值的列从另一个表中选择值

Table 1st :

表1:

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

Table 2nd :

表2:

-------------------------------------------------------------------
    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 ?

还有其他选择吗?

3 个解决方案

#1

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,则需要在连接的两侧执行此操作。

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

您需要在单个值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.

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

#3

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

#1