将不同的行合并为一对多结构

Question

我需要将数据迁移到具有不同结构的新数据库。

源数据类似于：

ID          Name       Surname    IDNumber Passport Mobile Email
----------- ---------- ---------- -------- -------- ------ ---------------
1           Nikola     Jokic      I123     P123     NULL   NULL
2           Nikola     Jokic      I123     NULL     M123   jokic@mail.com
3           Nikola     Jokic      NULL     P123     NULL   jokara@mail.com
4           Nikola     Jokic      NULL     NULL     NULL   NULL
5           Milos      Teodosic                            teo@mail.com
6           Milos      Teodosic            P333     M111   
7           Milos      Teodosic            P222     M111   teo@mail.com
8           Nikola     Jokic                               

问题是没有一个单独的列可以用来作为行之间的连接。

例如: ID为1和2的行通过id号连接，1和3通过Passport连接，因此id为1、2和3的行是单个客户。手机或电子邮件也可以连接。id为4的行只有name和surname，所以它不能与任何其他行连接，也不能与8连接。

我已经尝试过使用游标，但问题是源数据中的行数超过100k，而且速度非常慢。

此外，我正在考虑MERGE语句，但问题是源是一个表，但目标包含两个表，这是不受支持的。

CREATE TABLE [dbo].[Source](
    [ID] [int] NOT NULL IDENTITY,
    [Name] [nvarchar](50) NOT NULL,
    [Surname] [nvarchar](50) NOT NULL,
    [IDNumber] [nvarchar](50) NULL,
    [Passport] [nvarchar](50) NULL,
    [Mobile] [nvarchar](50) NULL,
    [Email] [nvarchar](50) NULL
) 
GO

CREATE TABLE [dbo].[Customer](
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [Name] [NVARCHAR](50) NULL,
    [Surname] [NVARCHAR](50) NULL,
    [IDNumber] [NVARCHAR](50) NULL
)
GO

CREATE TABLE [dbo].[CustomerInfo](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NOT NULL,
    [InfoType] [nvarchar](50) NOT NULL,
    [Value] [nvarchar](50) NOT NULL
)
GO



--this is desired result



ID          Name       Surname    IDNumber CustomerInfoID InfoType   Value
----------- ---------- ---------- -------- -------------- ---------- ---------------
1           Nikola     Jokic      I123     1              Passport   P123           
1           Nikola     Jokic      I123     2              Mobile     M123           
1           Nikola     Jokic      I123     3              Email      jokic@mail.com 
1           Nikola     Jokic      I123     4              Email      jokara@mail.com
4           Nikola     Jokic      NULL     NULL           NULL       NULL
5           Milos      Teodosic   NULL     5              Passport   P333           
5           Milos      Teodosic   NULL     6              Passport   P222           
5           Milos      Teodosic   NULL     6              Mobile     M111           
5           Milos      Teodosic   NULL     7              Email      teo@mail.com   
8           Nikola     Jokic      NULL     NULL                                     

Answer 1

这是你的第一步：

with sourcedata 
as
(
    select Id, Name, Surname, IdNumber, u.InfoType, u.Value
    from Stack.Source S
    Unpivot (
        Value for InfoType in ([Passport],[Mobile],[Email])
    ) u
)

select distinct 
        DENSE_RANK() over (order by Name, Surname) as TheNewId
       ,Name
       ,Surname
       ,IdNumber
       ,InfoType
       ,Value 
from sourcedata

我已经为display purpouse创建了一个简单的CTE，但您可以将其更改为(索引)视图以提高性能。

在这里，您可以对这两个表运行INSERT INTO .. SELECT。