请教：一个关于datagrid的问题

Question

我有两张表，一张名为SectionOffices，存放科室名。另一张名为Rooms存放房间相关信息，包括：此房间属于哪个科室，因为每个房间所属的科室具有不定性，所以还有一个WeekDay的字段，表示星期几此房间属于哪个科室。  
现在，我要使datagrid第一列显示所有科室，后面的列显示星期一到星期日，每个科室所分配的房间数。  
偶刚学C#.NET，用了个笨方法，但只能显示第一列的科室名，后面的都是空信息，可能是因为我一上来就绑定过了的关系，所以得到的值一个个插不进去，请大家指点。下面是我的代码：  
private  void  BindGrid()  
           {  
SqlConnection  conn=new  SqlConnection(ConfigurationSettings.AppSettings["connString"]);  
                                   conn.Open();  
                                   DataSet  ds=new  DataSet();  
                                   string  count="SELECT  count(*)  from  SectionOffices";  
                                   SqlCommand  comm=new  SqlCommand(count,conn);  
                                   int  i=Convert.ToInt16((comm.ExecuteScalar()));  
                                   string  sel="SELECT  Name  From  SectionOffices  WHERE  Name<>'所有科室'";  
                                   SqlDataAdapter  da=new  SqlDataAdapter(sel,conn);  
                                   da.Fill(ds);  
                                   this.dgShowOperNumBySectionName.DataSource=ds;  
                                   this.dgShowOperNumBySectionName.DataBind();                                      
                                     
                                   for(int  j=0;j<i-1;j++)  
                                   {                                                  
                                               for(int  wd=1;wd<8;wd++)  
                                               {                                  
                                                           string  sectionname=this.dgShowOperNumBySectionName.Items[j].Cells[0].Text;  
                                                           Room  myRoom=new  Room();  
                                                           string  returnvalue;  
                                                           returnvalue=myRoom.ShowSections(sectionname,wd.ToString());                                                              
                                                           returnvalue=this.dgShowOperNumBySectionName.Items[j].Cells[wd].Text;  
                                               }  
                                   }                                      
                       }  
 
public  string  ShowSections(string  sectionname,string  weekday)  
                       {  
                                   SqlConnection  conn=new  SqlConnection  (ConfigurationSettings.AppSettings["connString"]);  
                                   DataSet  ds=new  DataSet();  
                                   conn.Open();  
                                   int  a;  
                                   string  returnvalue;  
                                   string  strSEL="select  count(Name)  from  Rooms  Group  by  BelongedSection,RoomType,  WeekDay  having  WeekDay='"+weekday+"'and  RoomType='手术室'  AND  BelongedSection='"+sectionname+"'";  
                                   SqlCommand  comm=new  SqlCommand(strSEL,conn);  
                                   a=Convert.ToInt16(comm.ExecuteScalar());  
                                   if(a==0)  
                                   {  
                                               returnvalue=null;  
                                   }  
                                   else  
                                   {  
                                               returnvalue=comm.ExecuteScalar().ToString();  
                                   }  
                                   return  returnvalue;  
                       }  

Answer 1

select A.科室名称,B.房间 AS 房间1,,C.房间 AS 房间2,D.房间 AS 房间3,E.房间 AS 房间4,F.房间 AS 房间5,G.房间 AS 房间6,H.房间 AS 房间7 from SectionOffices A 
left outer join Rooms B ON A.科室ID=B.科室ID  and B.星期几='星期一'
left outer join Rooms C ON A.科室ID=C.科室ID  and C.星期几='星期二'
left outer join Rooms D ON A.科室ID=D.科室ID  and D.星期几='星期三'
left outer join Rooms E ON A.科室ID=E.科室ID  and E.星期几='星期四'
left outer join Rooms F ON A.科室ID=F.科室ID  and F.星期几='星期五'
left outer join Rooms G ON A.科室ID=G.科室ID  and G.星期几='星期六'
left outer join Rooms H ON A.科室ID=H.科室ID  and H.星期几='星期日'

Answer 2

谢谢大家的提示，我后来是这么做的 ：）
select a.Name,Number1=(select count (*) from Rooms where BelongedSection=a.Name and WeekDay=1),
Number2=(select count (*) from Rooms where BelongedSection=a.Name and WeekDay=2),
Number3=(select count (*) from Rooms where BelongedSection=a.Name and WeekDay=3),
Number4=(select count (*) from Rooms where BelongedSection=a.Name and WeekDay=4),
Number5=(select count (*) from Rooms where BelongedSection=a.Name and WeekDay=5),
Number6=(select count (*) from Rooms where BelongedSection=a.Name and WeekDay=6),
Number7=(select count (*) from Rooms where BelongedSection=a.Name and WeekDay=7)
from SectionOffices a where a.Name<>'所有科室'