Amalgamating rows in a table and removing NULL val

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources