How to combine rows of data - 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

How to combine rows of data

Author

Topic

bbarr
Starting Member

2 Posts

Posted - 2010-10-22 : 09:38:06

I have a report that is putting out data like this

Account Number AttendingPhys Admitting Phys

101 John NULL

101 NULL Peter

1002 David NULL

1002 NULL John

Do you know how to combine the two rows to get:

101 John Peter

1002 David John

Thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-10-22 : 10:22:29

You can take a max on AttendingPhys and Admitting Phys
and group by Account Number

Jim

Everyday I learn something that somebody else already knew

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-22 : 10:26:27

Not sure is this you are looking for

Select AccountNumber, Max(AttendingPhys), Max(Admitting Phys) from YourTAble
Group by AccountNumber

I am here to learn from Masters and help new bees in learning.

bbarr
Starting Member

2 Posts

Posted - 2010-10-22 : 11:06:09

I tried adding max to the code, but this did not fix the problem. I thought Max only worked on Numeric Records?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-10-22 : 11:34:08

DECLARE @Table TABLE (id int,col1 varchar(10),col2 varchar(10))
INSERT INTO @Table

SELECT '101','John', NULL UNION ALL

SELECT '101',NULL,'Peter' UNION ALL

SELECT '1002','David', NULL UNION ALL

SELECT '1002',NULL,'John'

SELECT id,max(col1),max(col2)
from @table
group by id

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources