How to merge many rows with the same ID with SQL Server

Question

I need the help from the community to solve the following problem that I found (I'm using SQL Server):

I have two tables, USER and INFO.

USER:

ID    NAME
----------
AAA   John
BBB   Mike

INFO:

ID      CONTANCT_INFO    CONTACT_DESC
-------------------------------------
AAA        111111           Phone
AAA        222222           Mobile
AAA    [email protected]      Email
AAA    [email protected]      Email
BBB        333333           Phone
BBB    [email protected]      Email
BBB    [email protected]      Email
BBB    [email protected]      Email
BBB        444444           Phone
BBB        555555           Mobile

I want to merge the info table per user in one row as follows:

ID  NAME    Phone1  Phone2  Phone3  Email1          Email2          Email3
---------------------------------------------------------------------------
AAA John    111111  222222          [email protected] [email protected] 
BBB Mike    333333  444444  555555  [email protected] [email protected] [email protected]

I've been trying many options and also surfing through stackoverflow posts, but without any results to my situation.

Thanks in advance!

Kind regards,

Aovon.

You must search about PIVOT and UNPIVOT functions, but... what would happen if there is a fourth register for AAA in INFO table? Do you want another field called Phone4? This could be a problem, you should fix a limit for the fields. — Carlos
– Carlos, Commented Dec 12, 2019 at 19:06
Hi Carlos, thanks for the response, the limit for phones are 3 and also for the emails. — Aovon
– Aovon, Commented Dec 12, 2019 at 19:11

Community · Accepted Answer · 2020-06-20 09:12:55Z

If you are sure that there will be only 3 phones and emails then following query will work:

;with PivotData as
(
select I.id,
       U.Name,
       I.CONTANCT_INFO,
       Replace(I.CONTACT_DESC,'Mobile','Phone') + '_'+
       cast( row_number() over (partition by I.id,
                                Replace(I.CONTACT_DESC,'Mobile','Phone') 
                                order by (Select null))--change here for sort order
       as char(1)) as CONTACT_DESC_Attribute
From Info I 
join [User] U on U.id = I.Id

)
SELECT id,Name,[Phone_1],[Phone_2],[Phone_3],
       [Email_1],[Email_2],[Email_3]
FROM PivotData
  PIVOT( Max(CONTANCT_INFO)
    FOR CONTACT_DESC_Attribute IN ([Phone_1],[Phone_2],[Phone_3],
                                   [Email_1],[Email_2],[Email_3]) ) AS P;

Sample code here..

Use dynamic query for pivoting as:

 DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' 
                   + QUOTENAME(Replace(I.CONTACT_DESC,'Mobile','Phone') + '_'+
                               cast( row_number() over (partition by I.id,
                                Replace(I.CONTACT_DESC,'Mobile','Phone') 
                                order by (Select null))--change here for sort order
                      as char(1))) 
            FROM Info I
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id,Name, ' + @cols + ' from 
            (
               select I.id,
                      U.Name,
                      I.CONTANCT_INFO,
                      Replace(I.CONTACT_DESC,''Mobile'',''Phone'') + ''_''+
                      cast( row_number() over (partition by I.id,
                                Replace(I.CONTACT_DESC,''Mobile'',''Phone'') 
                                order by (Select null))--change here for sort order
                      as char(1)) as CONTACT_DESC_Attribute
                From Info I 
                join [User] U on U.id = I.Id

           ) x
            pivot 
            (
                 max(CONTANCT_INFO)
                for CONTACT_DESC_Attribute in (' + @cols + ')
            ) p '
            
execute(@query);

Sample code here..

Hope this helps!!!

Thanks @Deepshikha, the first query using PIVOT has solved my problem. Thank you so much, really appreciated. Kind regards!

Tim · Accepted Answer · 2019-12-12 21:01:24Z

Here's one approach to consider. You may want to change the column naming/numbering method to meet your needs, but the output is what you're looking for.

IF OBJECT_ID('tempdb..#USER') IS NOT NULL DROP TABLE #USER
IF OBJECT_ID('tempdb..#INFO') IS NOT NULL DROP TABLE #INFO

CREATE TABLE #USER
(
ID      VARCHAR(10)
,NAME   VARCHAR(10)
)

CREATE TABLE #INFO
(
iPOS            INT IDENTITY (1,1)
,ID             VARCHAR(10)
,CONTACT_INFO   VARCHAR(100)
,CONTACT_DESC   VARCHAR(100)
)

INSERT INTO #USER
(ID, NAME)
SELECT 'AAA', 'JOHN'
UNION
SELECT 'BBB', 'MIKE'


INSERT INTO #INFO
(ID, CONTACT_INFO, CONTACT_DESC)
SELECT 'AAA', '111111', 'Phone'
UNION
SELECT 'AAA', '222222', 'Mobile'
UNION
SELECT 'AAA', '[email protected]', 'Email'
UNION
SELECT 'AAA', '[email protected]', 'Email'
UNION
SELECT 'BBB', '333333', 'Phone'
UNION
SELECT 'BBB', '[email protected]', 'Email'
UNION
SELECT 'BBB', '[email protected]', 'Email'
UNION
SELECT 'BBB', '[email protected]', 'Email'
UNION
SELECT 'BBB', '444444', 'Phone'
UNION 
SELECT 'BBB', '555555', 'Mobile'

UPDATE A
SET CONTACT_DESC = CONTACT_DESC + CAST(iPOS AS VARCHAR)
FROM #INFO A

DECLARE @COL_NAME       VARCHAR(2000) = ''
DECLARE @SQL            NVARCHAR(MAX) = ''

SELECT  @COL_NAME = STUFF(( SELECT 
                        '], [' + CONTACT_DESC 
                        FROM (
                                SELECT 
                                    CONTACT_DESC
                                FROM #INFO A
                        ) AS x
                        ORDER BY 1
                        FOR XML PATH('')
                        ), 1, 2, '') + ']'

SET @SQL = 'SELECT ID, NAME, ' + @COL_NAME + '  
            FROM (
              SELECT
                     U.ID, U.NAME, I.CONTACT_INFO, I.CONTACT_DESC
                FROM #USER U
                    INNER JOIN #INFO I
                        ON U.ID = I.ID
            ) AS j
            PIVOT
            (
            MAX(CONTACT_INFO) 
            FOR CONTACT_DESC IN ('
              + @COL_NAME
              + ')
            ) AS S
            '
PRINT @sql
EXEC sp_executesql @sql

This has an output of the following:

Thanks @Tim for your response, I will take a look into your proposal too. Kind regards!

Collectives™ on Stack Overflow

How to merge many rows with the same ID with SQL Server

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related