时间戳

首页 > 解决方案 > SSIS查找一个表中的多个列到另一个表中的相同ID列

sql-server - SSIS查找一个表中的多个列到另一个表中的相同ID列

问题描述

我有下表：

EventValue | Person1           | Person2    | Person3     | Person4       | Meta1  | Meta2 
-------------------------------------------------------------------------------------------
123        | joePerson01       | samRock01  | nancyDrew01 | steveRogers01 | 505   | 606
321        | steveRogers02     | yoMama01   | ruMo01      | lukeJedi01    | 707   | 808

我想将 Person 列转换为目标表的 ID，因此所有 ID 都来自Person目标数据库中的同一个表：

ID | FirstName | LastName  | DatabaseOneID | DatabaseTwoID
----------------------------------------------------------
1  | Joe       | Person    | joePerson01   | personJoe01
2  | Sam       | Rockwell  | samRock01     | rockSam01
3  | Nancy     | Drew      | nancyDrew01   | drewNancy01
4  | Steve     | Rogers    | steveRogers01 | rogersSteve01
5  | Steve R   | Rogers    | steveRogers02 | rogersSteve02
6  | Yo        | Mama      | yoMama01      | mamaYo01
7  | Rufus     | Murdock   | ruMo01        | moRu01
8  | Luke      | Skywalker | lukeJedi01    | jediLuke01

结果如下：

MetaID     | EventValue | Person1ID | Person2ID | Person3ID | Person4ID
------------------------------------------------------------------------
1          | 123        | 1         | 2         | 3         | 4
2          | 321        | 5         | 6         | 7         | 8

我目前有一个查找转换来查找第一个 Person 列，但无法弄清楚如何在同一个查找中将所有 4 个 Person 列转换为 ID。

标签： sql-serverssis

解决方案

UNPIVOT如果您认为它更适合您的实现，您可以在一个查询中执行此操作，或者使用或使用标量函数。然后，您只需创建它的视图，您可以轻松访问它。

这是一个简单的例子：

DECLARE 
    @tb1 TABLE 
(
    EventValue INT
, Person1 VARCHAR(250)
, Person2 VARCHAR(250)
, Person3 VARCHAR(250)
, Person4 VARCHAR(250)
, Meta1 INT 
, Meta2 INT 
)

DECLARE 
    @Person TABLE 
(
    ID INT 
,   FirstName VARCHAR(250)
,   LastName  VARCHAR(250)
,   DatabaseOneID VARCHAR(250)
,   DatabaseTwoID VARCHAR(250)
)


INSERT INTO @tb1 
VALUES 
(123,'joePerson01','samRock01','nancyDrew01','steveRogers01',505,606),
(321,'steveRogers02','yoMama01','ruMo01','lukeJedi01',707,808)


INSERT INTO @Person 
VALUES 
(1,'Joe','Person','joePerson01','personJoe01'),
(2,'Sam','Rockwell','samRock01','rockSam01'),
(3,'Nancy','Drew','nancyDrew01','drewNancy01'),
(4,'Steve','Rogers','steveRogers01','rogersSteve01'),
(5,'SteveR','Rogers','steveRogers02','rogersSteve02'),
(6,'Yo','Mama','yoMama01','mamaYo01'),
(7,'Rufus','Murdock','ruMo01','moRu01'),
(8,'Luke','Skywalker','lukeJedi01','jediLuke01')

SELECT ROW_NUMBER() OVER(ORDER BY EventValue) AS MetaID, *
FROM (
    SELECT 
        t.EventValue 
    ,   MAX(CASE WHEN t.Person1 IN(p.DatabaseOneID, p.DatabaseTwoID) THEN p.ID ELSE NULL END) AS Person1ID
    ,   MAX(CASE WHEN t.Person2 IN(p.DatabaseOneID, p.DatabaseTwoID) THEN p.ID ELSE NULL END) AS Person2ID
    ,   MAX(CASE WHEN t.Person3 IN(p.DatabaseOneID, p.DatabaseTwoID) THEN p.ID ELSE NULL END) AS Person3ID
    ,   MAX(CASE WHEN t.Person4 IN(p.DatabaseOneID, p.DatabaseTwoID) THEN p.ID ELSE NULL END) AS Person4ID
    FROM @tb1 t
    LEFT JOIN @Person p 
        ON p.DatabaseOneID IN(t.Person1, t.Person2, t.Person3, t.Person4)
        OR p.DatabaseTwoID IN(t.Person1, t.Person2, t.Person3, t.Person4)
    GROUP BY t.EventValue
) D

推荐阅读