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)
 Amalgamating rows in a table and removing NULL val

Author  Topic 

stamford
Starting Member

47 Posts

Posted - 2014-03-24 : 13:30:59
What script would amalgamate rows by ID in the following table and remove NULL values where an ID appears more than once. So for instance ID 3 and ID 217 would become as below. The script has to be old school to be compatible with SQL2000.


ID PreOpOrg PreOpTreatment PostOpOrg PostOpTreatment
3 RBA11 02 RBA11 06
217 NULL NULL RN325 02


and so on


ID PreOpOrg PreOpTreatment PostOpOrg PostOpTreatment
3 RBA11 02 NULL NULL
3 NULL NULL RBA11 06
217 NULL NULL RN325 02
364 NULL NULL RBA11 02
369 NULL NULL RN325 02
481 GR123 05 NULL NULL
834 RBA11 02 NULL NULL
834 NULL NULL RBA11 04
1066 NULL NULL RBA11 05
2123 NULL NULL RBA11 05
2246 NULL NULL RBA11 02
2246 RBA11 02 NULL NULL
2512 RBA11 04 NULL NULL
2512 NULL NULL RBA11 06
2694 NULL NULL RN325 05
2892 NULL NULL RBA11 06
2892 RBA11 05 NULL NULL
3311 RBA11 05 NULL NULL
3311 NULL NULL RBA11 06
3344 RBA11 02 NULL NULL
3362 RBA11 02 NULL NULL
3770 RBA11 05 NULL NULL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-03-24 : 13:52:06
Try this:

select
ID,
max(PreOpOrg) as PreOpOrg,
max(PreOpTreatment) as PreOpTreatment,
max(PostOpOrg) as PostOpOrg,
max(PostOpTreatment) as PostOpTreatment
from YourTable
group by ID


Too old to Rock'n'Roll too young to die.
Go to Top of Page

stamford
Starting Member

47 Posts

Posted - 2014-03-24 : 14:09:23
quote:
Originally posted by webfred

Try this:

select
ID,
max(PreOpOrg) as PreOpOrg,
max(PreOpTreatment) as PreOpTreatment,
max(PostOpOrg) as PostOpOrg,
max(PostOpTreatment) as PostOpTreatment
from YourTable
group by ID


Too old to Rock'n'Roll too young to die.



Of course - it seems so simple now. Thanks dude
Go to Top of Page
   

- Advertisement -