Error in query

Hi,

When i execute this procedure
select s.schoolcode, s.SchoolName ,s.mobileno, CONVERT(varchar(11),d.fooddates,103) AS fooddates , fi.items ,f.type, f.totalno, f.rate , f.totalamount
from rangotsav_schools s , rangotsav_fooddates d, rangotsav_food f , rangotsav_fooditems fi
where f.schoolid not in ( 1 , 2) and totalno != 0 and f.schoolid = s.schoolid and f.dateid = d.dateid and f.fooditemid = fi.fooditemid
order by s.SchoolName , d.fooddates

getting result as
code Schoolname mobileno fooddates items type no rate amount
KE048 school1 9744204108 09/09/2015 Dinner Veg 24 80 1920
KE048 School2 9744204108 10/09/2015 Breakfast 24 60 1440
KE048 School2 9744204108 10/09/2015 Lunch NonVeg 24 90 2160
KE048 School2 9744204108 10/09/2015 Tea 20 20 400
KE147 School3 9846529544 10/09/2015 Breakfast 36 60 2160
KE147 School3 9846529544 10/09/2015 Lunch NonVeg 36 90 3240

But i need the result as
code, schoolname ,mobileno, 09/09/2015 (Dinner) (total nos) , 09/09/2015 Dinner (Amount) ,
10/09/2015(Breakfast)( 24) , 10/09/2015(Breakfast) (Amount) , 10/09/2015 (Lunch) (NonVeg) (24),
10/09/2015 (Lunch) (NonVeg) (Amount)

Each schools details in one row itself.
Can anyone please help me?

Looks like homework, but you should look up FOR XML PATH

To help you better, please post:

  1. CREATE TABLE statements for all the tables involved
  2. INSERT INTO statements to populate the tables in step 1 with sample data

Also, please use standard join syntax, e.g.

SELECT ...
FROM x
JOIN  y on x.id = y.id
...etc.