Excel Import + combining rows with some duplicate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Excel Import + combining rows with some duplicate

Author

Topic

flipfyre
Starting Member

26 Posts

Posted - 2009-03-12 : 00:33:05

Coders,

I'm merging 2 excel files into 1 table, which works fine. One excel file contains data for the first 6 months of the year, and the 2nd excel file contains data for the last 6 months of the year. Once this is inserted in the database, there are some duplicate columns I'd like to merge, but some columns are not duplicates, like so: (first row is the header)

ID NAME CODE POS JAN FEB MAR APR MAY JUN
1 John 3421 MGR OFF OFF OFF NULL NULL NULL
2 John 3421 MGR NULL NULL NULL VAC VAC VAC

So, if everything was the same, I could do a UNION or perhaps an INTERSECT; however, only part of these rows are the same and part are different. I'd like to merge the Name, Code, Pos, and then also combine the rest where the values are not NULL, so the final output would be like:

John 3421 MGR OFF OFF OFF VAC VAC VAC

So, my question is 1) How can I do this, and 2)Can I do this during the SSIS Import of the excel tables so that I do this once, and don't put any more load/time on the server.

Thanks in advance guys!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-12 : 13:51:46

seems like what you need is a merge join task

flipfyre
Starting Member

26 Posts

Posted - 2009-03-13 : 09:52:20

Sorry, I cross posted, but a guy named Peso gave me this excellent solution:

SELECT ID,
NAME,
CODE,
POS,
MAX(COALESCE(JAN, '')) AS JAN,
MAX(COALESCE(FEB, '')) AS FEB,
MAX(COALESCE(MAR, '')) AS MAR,
MAX(COALESCE(APR, '')) AS APR,
MAX(COALESCE(MAY, '')) AS MAY,
MAX(COALESCE(JUN, '')) AS JUN
FROM Table1
GROUP BY ID,
NAME,
CODE,
POS

Here's the link: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=121539[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 13:56:50

will work fine as long you dont have more than one non null come in any of month columns for an id.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources