SQL 将重复记录合并到一行

Question

我有一个 SQL Server 2016 数据库，其中有几千条记录。

当前显示的记录如下；

我希望得到这样的结果；

然后我会将数据导出到 Excel。

本质上，一个人和多个帐户类型有多个联系详细信息，而不是每个详细信息占用一个新行，我需要将它们全部折叠到一个带有添加列的单行上，我可以创建一个新的数据库，其中包含如果需要插入列。

我尝试过这些解决方案的变体；

By Taryn

By Hassan - 我不知道我需要加入什么以及在哪里加入。

By Brad C - 我在这方面取得了一些成功，因为它截断了该数据库中的表。

还有其他一些，但链接已被隐藏，抱歉。

架构

CREATE TABLE [dbo].[Contacts] 
(
    [emplid] [float] NULL,
    [emcoid] [float] NULL,
    [name] [varchar](50) NULL,
    [conttp] [varchar](50) NULL,
    [phone] [varchar](50) NULL,
    [fax] [varchar](50) NULL,
    [email] [varchar](50) NULL,
    [auth] [float] NULL,
    [ainits] [varchar](50) NULL,
    [adate] [datetime] NULL,
    [atime] [datetime] NULL,
    [uinits] [varchar](50) NULL,
    [udate] [datetime] NULL,
    [utime] [datetime] NULL
) ON [PRIMARY]
GO

测试数据

INSERT [dbo].[Contacts_NEW] ([emplid], [emcoid], [name], [conttp], [phone], [fax], [email], [auth], [ainits], [adate], [atime], [uinits], [udate], [utime]) 
VALUES (100, 103, N'MR Bert Ernie', N'PENS', N'1800100300', NULL, N'bert.ernie@mail.com', 1, N'MK785487', CAST(N'2016-08-17T00:00:00.000' AS DateTime), CAST(N'2068-02-07T00:00:00.000' AS DateTime), N'MK785487', CAST(N'2016-08-17T00:00:00.000' AS DateTime), CAST(N'2068-02-07T00:00:00.000' AS DateTime))
INSERT [dbo].[Contacts_NEW] ([emplid], [emcoid], [name], [conttp], [phone], [fax], [email], [auth], [ainits], [adate], [atime], [uinits], [udate], [utime]) 
VALUES (100, 104, N'MR Bert Ernie', N'OFFI', N'1800100300', NULL, N'bert.ernie@mail.com', 1, N'MK785487', CAST(N'2016-08-17T00:00:00.000' AS DateTime), CAST(N'2068-02-07T00:00:00.000' AS DateTime), N'MK785487', CAST(N'2016-08-17T00:00:00.000' AS DateTime), CAST(N'2068-02-07T00:00:00.000' AS DateTime))
INSERT [dbo].[Contacts_NEW] ([emplid], [emcoid], [name], [conttp], [phone], [fax], [email], [auth], [ainits], [adate], [atime], [uinits], [udate], [utime])    
VALUES (100, 105, N'MR Bert Ernie', N'CONT', N'1800100300', NULL, N'bert.ernie@mail.com', 1, N'MK785487', CAST(N'2016-08-17T00:00:00.000' AS DateTime), CAST(N'2068-02-07T00:00:00.000' AS DateTime), N'MK785487', CAST(N'2016-08-17T00:00:00.000' AS DateTime), CAST(N'2068-02-07T00:00:00.000' AS DateTime))
INSERT [dbo].[Contacts_NEW] ([emplid], [emcoid], [name], [conttp], [phone], [fax], [email], [auth], [ainits], [adate], [atime], [uinits], [udate], [utime]) 
VALUES (200, 113, N'Roger Federer', N'PENS', N'78415784156', NULL, N'nomail@nomail.co.uk', 1, N'MK785477', CAST(N'2016-08-17T00:00:00.000' AS DateTime), CAST(N'2068-02-07T00:00:00.000' AS DateTime), N'MK785477', CAST(N'2016-08-17T00:00:00.000' AS DateTime), CAST(N'2068-02-07T00:00:00.000' AS DateTime))
INSERT [dbo].[Contacts_NEW] ([emplid], [emcoid], [name], [conttp], [phone], [fax], [email], [auth], [ainits], [adate], [atime], [uinits], [udate], [utime]) 
VALUES (200, 114, N'Roger Federer', N'OFFI', N'78415784157', NULL, N'Yourmail@nomail.co.uk', 1, N'MK785477', CAST(N'2016-08-17T00:00:00.000' AS DateTime), CAST(N'2068-02-07T00:00:00.000' AS DateTime), N'MK785477', CAST(N'2016-08-17T00:00:00.000' AS DateTime), CAST(N'2068-02-07T00:00:00.000' AS DateTime))

如果有更多时间，我也许能够弄清楚，但就目前情况而言，我被难住了。我不喜欢将其导出到 Excel 并手动操作所有记录。

任何帮助，或者正确方向的指出，我们将不胜感激。

谢谢。

Answer 1

条件聚合可以相当轻松地处理这个问题。具有挑战性的部分是那些电子邮件，但并非不可能。但是，在示例输出中只有 17 列 (emcoid 1 - 4)。如果您需要调整它以适应第五个，就很容易了。

select x.emplid
    , x.name
    , conttp = max(case when x.RowNum = 1 then x.conttp end)
    , conttp2 = max(case when x.RowNum = 2 then x.conttp end)
    , conttp3 = max(case when x.RowNum = 3 then x.conttp end)
    , conttp4 = max(case when x.RowNum = 4 then x.conttp end)
    , conttp5 = max(case when x.RowNum = 5 then x.conttp end)
    , conttp6 = max(case when x.RowNum = 6 then x.conttp end)
    , phone = max(case when x.RowNum = 1 then x.phone end)
    , phone2 = max(case when x.RowNum = 2 then x.phone end)
    , x.fax
    , email = max(case when x.RowNum = 1 then emails.email end)
    , email2 = max(case when x.RowNum = 2 then emails.email end)
    , emcoid1 = max(case when x.RowNum = 1 then x.emcoid end)
    , emcoid2 = max(case when x.RowNum = 2 then x.emcoid end)
    , emcoid3 = max(case when x.RowNum = 3 then x.emcoid end)
    , emcoid4 = max(case when x.RowNum = 4 then x.emcoid end)
from 
(
    select *
        , RowNum = ROW_NUMBER() over(partition by emplid order by emcoid)
    from Contacts c
) x
join
(
    select *
        , RowNum = ROW_NUMBER() over(partition by emplid order by (select null))
    from
    (
        select distinct email
            , emplid
        from Contacts
    ) a
) emails on emails.RowNum = x.RowNum
            and emails.emplid = x.emplid
group by x.emplid
    , x.name
    , x.fax