Combining rows with some duplicate columns

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

Transact-SQL (2005)

Combining rows with some duplicate columns

Author

Topic

flipfyre
Starting Member

26 Posts

Posted - 2009-03-12 : 11:45:20

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!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 11:49:38

[code]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[/code]

E 12°55'05.63"
N 56°04'39.26"

flipfyre
Starting Member

26 Posts

Posted - 2009-03-12 : 16:57:56

Thanks Peso! Works like a charm. One note just in case anyone else is trying to use this, make sure to add the extra parenthesis at the end of the statement: MAX(COALESCE(JAN, '')) AS JAN. Also, I believe I can just create a view that hits this table and incorporate this code unless, there is a way to do this upon the import of the excel tables, but I'll work that out. Again, thanks for the quick reply and solution Peso!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources