Combine Multiple Rows into One Row

I have an Excel spreadsheet with multiple records for a company.  The name, address, etc is all the same.  The difference is an expired date.  Each individual row has owner expired date then manager expired date then business expired date then insurance expired date.  

Company Name Address City State Zip Type Expired
Comp A 123 St Best NY 12345 Owner 5/1/2016
Comp A 123 St Best NY 12345 Manager 5/1/2016
Comp A 123 St Best NY 12345 Business 5/23/2016
Comp A 123 St Best NY 12345 Insurance 12/31/2016

I imported the data into a database table with hopes of being able to have it changed to the table below, but can't get it to work.

Company Name Address City State Zip Owner Expired Manager Expired Business Expired Insurance Expired
Comp A 123 St Best NY 12345 5/1/2016 5/1/2016 5/23/2016 5/23/2016

Answer
Answer

You could try this cross tab query:

Create a new query using Query design mode. Then switch to SQL view and paste the following query into the SQL window.

Replace "YourTable" with the actual table name:

---------------------------------------------------------------------------------------------------------------------------

TRANSFORM Last(YourTable.[Expired]) AS LastOfExpired
SELECT YourTable.[Company Name], YourTable.[Address], YourTable.[City], YourTable.State, YourTable.Zip
FROM YourTable
GROUP BY YourTable.[Company Name], YourTable.[Address], YourTable.[City], YourTable.State, YourTable.Zip
PIVOT YourTable.[Type];
--------------------------------------------------------------------------------------------------------------------------

This will return the last instance of the expired dates. I f you want the first set change the last function to First. (You can also use Min or Max.

YourTable_Crosstab
Comp A 123 St Best NY 12345 5/23/2016 12/31/2016 5/1/2016 5/1/2016

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated January 19, 2021 Views 1,357 Applies to: