SQL Merge duplicate records to one row

Question

I have a SQL Server 2016 database with a couple thousand records in.

The records are displayed currently like so;

And i would like to have this outcome;

I will then export the data to Excel.

Essentially there are multiple contact details for one person and multiple account types, instead of each detail taking up a new row, I need to collapse them all onto one single row with added columns, I can create a new db that has the columns inserted if needed.

I have tried variation of these solutions;

By Taryn

By Hassan - I couldn't figure out what i needed to join and where.

By Brad C - I had some success with this in the fact it truncated the tables in this db.

And a few others but the links have gotten buried, I'm sorry.

Schema

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

Test Data

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

Given more time I may be able to figure it out, but as it stands I am stumped. And I don't fancy exporting this into excel and manipulating all the records by hand.

Any help would be appreciated, or a point in the right direction.

Thank you.

What if somebody has more than 2 phone numbers or email addresses? — Tab Alleman, Jul 13 '18 at 13:35
I prefer to use a dynamic cross tab (or conditional aggregation) for this especially when there are multiple groups of repeating values. http://www.sqlservercentral.com/articles/Crosstab/65048/ — Sean Lange, Jul 13 '18 at 13:36
@TabAlleman We are not accepting any more contacts and the data currently shows some have 2 of each. Moving forward there will be a limit of 4 of each. — Lloyd, Jul 13 '18 at 13:40
Are you wanting a fixed number of columns? It looks like it from the desired output. Conditional aggregation would be super simple for this. How do you decide the order of values? — Sean Lange, Jul 13 '18 at 13:43
Why did the `PIVOT` solution not work for you? That seems to be exactly what you are asking for. — Shawn, Jul 13 '18 at 13:53
@SeanLange Correct yes. 18 in total. As displayed above. 5x emcoid, 6x conttp, 2x phone, 2x email , fax, name, empid. Values are ordered by emplid (basically a location code) — Lloyd, Jul 13 '18 at 13:56
@Shawn It may well do, it was my first thought, but i couldn't quite get my head around how to figure it out. — Lloyd, Jul 13 '18 at 13:57
You can use a separate table for contact and email or use Unique for those columns — Bijay Budhathoki, Jul 13 '18 at 14:14

score 3 · Accepted Answer · answered Jul 13 '18 at 14:16

Conditional aggregation can handle this fairly easily. The challenging piece is those emails but not impossible at all. However, in your sample output there are only 17 columns (emcoid 1 - 4). Easy enough if you need to adjust this to accommodate a fifth.

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

That's it! Wow. Thank you so much! This has saved me so much time! — Lloyd, Jul 13 '18 at 14:28

SQL Merge duplicate records to one row

1 Answers1