merging rows - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

merging rows

Author

Topic

Inno
Starting Member

33 Posts

Posted - 2006-06-28 : 04:03:02

Say I have a table with the columns (and example data):

CustomerNo, ContactNo, ActivityNo
null, null, 1
100, null, 1
null, 666, 1
null, null, 2
200, null, 2
null, 777, 2

From this I would like to get the result:

CustomerNo, ContactNo, ActivityNo
100, 666, 1
200, 777, 2

How do I solve this. Im getting grey hair here...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 04:11:11

You should not allow null values in customerNo

Try this

Select max(CustomerNo) as CustomerNo, max(ContactNo) as ContactNo,ActivityNo from yourtable
group by ActivityNo

Madhivanan

Failing to plan is Planning to fail

Inno
Starting Member

33 Posts

Posted - 2006-06-28 : 04:30:37

Awsome. How do I create a temporary table with this data so I can use it in another transaction?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 04:36:50

Create table #t(....) --same structure as original table

Insert into #t
Select max(CustomerNo) as CustomerNo, max(ContactNo) as ContactNo,ActivityNo from yourTable
group by ActivityNo

Select * from #t

Madhivanan

Failing to plan is Planning to fail

Inno
Starting Member

33 Posts

Posted - 2006-06-28 : 04:54:39

You are the man madhivanan! Thanx.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 07:08:38

Also Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources