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?