ssrs combining rows into result set

Question

So I have a sql server 2005 query that returns results like so:

Address | Doctor
-----------------------
1         Dr A
1         Dr B
1         Dr C
2         NULL
3         NULL
4         Dr D
4         Dr E
5         Dr F

What I want is to get the output so that when I render the report I have them grouped together:

Dr A
Dr B
Dr C
Address1

Address2

Address3

Dr D
Dr E
Address4

Dr F
Address5

The problem is right now in my SSRS report I group by Address and it is only listing the first Dr and if the location has more then 1 Dr then they are not being listed.

Answer 1

Starting out, it looks like you have a LEFT join between the Doctors and the Address.

I think you should group by Doctor and put the Address into the detail section and they should all appear properly.

Answer 2

In order to do this on the sql side (which was required to render the report correctly) I had to use Coalesce which my exact situation was detailed in this link:

Coalesce example grouping up multiple rows