Solved

Merge Duplicate Rows with SQL "Merge"

Posted on 2011-02-25
15
578 Views
Last Modified: 2012-05-11
Hi Experts,

I have a SQL table with duplicate rows, that I would like to merge to a single row for each record.  below is what the existing table looks like.  I would like to process the whole table so the final results will just be unique.  Please use SQL to accomplish this task.
Table A as it exists now
ID             Unit    Date           TimeSent                          TimeArrived  
1234          12     1/1/2010    1/1/2010 20:10               1/1/2010 22:00
1235          8       1/2/2010    1/2/2010 20:10               1/2/2010 22:00
1234          35     1/1/2010    1/1/2010 20:10               1/1/2010 22:30
1235          10     1/1/2010    1/2/2010 20:10               1/2/2010 22:11

so the final table would look like this:
ID             Unit1   Unit2   Unit3    Date           TimeSent               TimeArrived1          Time Arrived2  
1234          12         35    NULL  1/1/2010    1/1/2010 20:10       1/1/2010 22:00       1/1/2010 22:30
1235          8           10    NULL  1/2/2010    1/2/2010 20:10       1/2/2010 22:00        1/2/2010 22:11

there are times when there are up to 6 units with 6 arrival times
thanks,

0
Comment
Question by:Aaron Goodwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 2
  • +1
15 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 34983586
try this
SELECT ID, MAX([1]),MAX([2]),MAX([3]),MAX([4]),MAX([5]),MAX([6]), Date, TimeSent, MIN(TimeArrived) AS T1, MAX(TimeArrived) AS T2
FROM
(  
	SELECT ID, [1],[2],[3],[4],[5],[6], Date, TimeSent, TimeArrived   
	FROM  
	(   
		SELECT ID, Unit, Date, TimeSent, TimeArrived, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TimeArrived) AS rowOrder  
		FROM pivotTable
	) AS P
	PIVOT 
	(
		MAX(Unit) FOR rowOrder IN ([1],[2],[3],[4],[5],[6])	
	) AS PVT
) A
GROUP BY ID, Date, TimeSent

Open in new window

0
 

Author Comment

by:Aaron Goodwin
ID: 34996627
trying now
0
 

Author Comment

by:Aaron Goodwin
ID: 34996736
its very close to what I need, thank you very much.  The only problem is when there are more than 2 units.  The time arrived does a min and max which is ok for up to 2 units.  But when I get 3 or more, those time values wont work.  Is there a way to modify your code so the correct arrived time will match the unit?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 3

Expert Comment

by:KetGuru
ID: 34996826

Full respect to tigin44 suggestion and I agree with tigin44 but i had doubt what if entire row is duplicate for specified column values. so i put extra distinct statement to avoid any such chances.

SELECT ID, MAX([1]),MAX([2]),MAX([3]),MAX([4]),MAX([5]),MAX([6]), Date, TimeSent, MIN(TimeArrived) AS T1, MAX(TimeArrived) AS T2
FROM
(  
      SELECT ID, [1],[2],[3],[4],[5],[6], Date, TimeSent, TimeArrived  
      FROM  
      (  
      SELECT  ID, Unit, Date, TimeSent, TimeArrived , ROW_NUMBER() OVER (PARTITION BY ID  ORDER BY TimeArrived) AS rowOrder  
            FROM
            (
            SELECT DISTINCT ID, UNIT, DATE,TIMESENT, TIMEARRIVED FROM
                  dbo.tblMergeRows
            )AS T
      ) AS P
      PIVOT
      (
            MAX(Unit) FOR rowOrder IN ([1],[2],[3],[4],[5],[6])      
      ) AS PVT
) A
GROUP BY ID, Date, TimeSent
0
 

Author Comment

by:Aaron Goodwin
ID: 34997402
Thanks KetGuru,

i am still having trouble with multiple units and their Time arrived.  In the example I gave, I mentioned that there could be up to 6 units with 6 timeArrived values.  How can I deal with those?

SELECT ID, MAX([1]),MAX([2]),MAX([3]),MAX([4]),MAX([5]),MAX([6]), Date, TimeSent, MIN(TimeArrived) AS T1, MAX(TimeArrived) AS T2

the Min and Max time arrived works fine for up to 2 units.....after that its not working.

Thanks again.
0
 
LVL 32

Expert Comment

by:awking00
ID: 34997528
What would your final table look like if the data were like this?
ID             Unit    Date           TimeSent                          TimeArrived  
1234          12     1/1/2010    1/1/2010 20:10               1/1/2010 22:00
1235          8       1/2/2010    1/2/2010 20:10               1/2/2010 22:00
1234          35     1/1/2010    1/1/2010 20:10               1/1/2010 22:30
1235          10     1/1/2010    1/2/2010 20:10               1/2/2010 22:11
1234          12     1/1/2010    1/1/2010 20:10               1/1/2010 22:45
1236          16     1/1/2010    1/1/2010 20:10               1/1/2010 22:15
0
 
LVL 32

Expert Comment

by:awking00
ID: 34997544
I actually meant to change the unit value for the third id of 1234 to something like 20.
0
 

Author Comment

by:Aaron Goodwin
ID: 34998021
Thanks Awking00,

It would look something like this, but for the case ID 1235 the ID would be from the same date.  It wouldn't have the same caseid on 2 different dates.  So essentially, the table would look like the attachment

table.doc
0
 
LVL 32

Expert Comment

by:awking00
ID: 34998448
See attached.
query.txt
0
 
LVL 3

Accepted Solution

by:
KetGuru earned 300 total points
ID: 34998535
here is my query for you.


SELECT aa.ID, max(aa.u1), max(aa.u2), max(aa.u3), max(aa.u4), max(aa.u5), max(aa.u6) , aa.Date, aa.TimeSent, max(bb.d1), max(bb.d2), max(bb.d3), max(bb.d4), max(bb.d5), max(bb.d6)

from
(
SELECT ID, MAX([1]) as 'u1',MAX([2]) as 'u2',MAX([3]) as 'u3',MAX([4]) as 'u4',MAX([5]) as 'u5',MAX([6]) as 'u6', Date, TimeSent --, MIN(TimeArrived) AS T1, MAX(TimeArrived) AS T2
FROM
(  
      SELECT ID, [1],[2],[3],[4],[5],[6], Date, TimeSent, TimeArrived  
      FROM  
      (  
      SELECT  ID, Unit, Date, TimeSent, TimeArrived , ROW_NUMBER() OVER (PARTITION BY ID  ORDER BY TimeArrived) AS rowOrder  
            FROM
            (
            SELECT DISTINCT ID, UNIT, DATE,TIMESENT, TIMEARRIVED FROM
                  dbo.tblMergeRows
                        --Group by ID
            )AS T
      ) AS P
      PIVOT
      (
            MAX(Unit) FOR rowOrder IN ([1],[2],[3],[4],[5],[6])      
      ) AS PVT
) A
GROUP BY ID, Date, TimeSent, timearrived
)as aa ,

(
SELECT ID, MAX([1])  as 'd1',MAX([2]) as 'd2',MAX([3]) as 'd3',MAX([4]) as 'd4',MAX([5]) as 'd5',MAX([6]) as 'd6', Date, TimeSent --, MIN(TimeArrived) AS T1, MAX(TimeArrived) AS T2
FROM
(  
      SELECT ID, [1],[2],[3],[4],[5],[6], Date, TimeSent
      FROM  
      (  
      SELECT  ID, Unit, Date, TimeSent, TimeArrived , ROW_NUMBER() OVER (PARTITION BY ID  ORDER BY TimeArrived) AS rowOrder  
            FROM
            (
            SELECT DISTINCT ID, UNIT, DATE,TIMESENT, TIMEARRIVED FROM
                  dbo.tblMergeRows
                        --Group by ID
            )AS T
      ) AS P
      PIVOT
      (
            MAX(TimeArrived) FOR rowOrder IN ([1],[2],[3],[4],[5],[6])      
      ) AS PVT
) A
GROUP BY ID, Date, TimeSent
) as bb

where aa.ID = bb.ID and aa.Date= bb.Date and aa.TimeSent= bb.TimeSent
Group by aa.ID, aa.Date, aa.TimeSent
0
 

Author Comment

by:Aaron Goodwin
ID: 35006868
checking out the codes from KetGuru and Awking00....thanks for the replies
0
 

Author Comment

by:Aaron Goodwin
ID: 35007106
Hi Awking00,

I must be doing something wrong, when I tried to run the code you gave me...it gave me syntax errors.  I made some subtle Changes, but when I do get it to run, the results was not a merged row.....is there a second part to this code?

Checking KetGuru's Code
query1-modified.txt
0
 

Author Comment

by:Aaron Goodwin
ID: 35007133
Awking00 these were the results I got from my modified query....again, I apologize if the results were from my own doings Un-merged-Results.xls
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 200 total points
ID: 35007903
I think we need to incorporate the max function and a group by to create the "merge" as KetGuru used. Try the attached.
query.txt
0
 

Author Closing Comment

by:Aaron Goodwin
ID: 35010928
Thank you very much for your ideas, works great!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question