Error in query

1

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?

2

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.