Pivot Query

Question

In my project i want to display the student attendance reports in Date wise..but i maintained a single table for storing all attendance details...In my table the records are stored as,

E.No Name Hourrs Date

225 Arun 9 08/25/2011

226 Jegankumar 8 08/25/2011

225 Arun 7.58 08/24/2011

226 Jegankumar 2.22 08/24/2011

225 Arun 8.36 08/23/2011

226 Jegankumar 9 08/23/2011

here i want a report in following format,

E.No Name 08/23/2011 08/24/2011 08/25/2011

225 Arun 8.36 7.58 9

226 Jegankumar 9 2.22 8

How to Write Query in pivot Table Please Send me any example.........

Gohil Jayendrasinh · Answer

hi ashok create table student ( [E.No] int  [Name] varchar(max) [Hourrs] numeric(32) [Date] datetime ) insert into student values (225'Arun'9'08/25/2011') insert into student values (226'Jegankumar'8'08/25/2011') insert into student values (225'Arun'7.58'08/24/2011') insert into student values (226'Jegankumar'2.22'08/24/2011') insert into student values (225'Arun'8.36'08/23/2011') insert into student values (226'Jegankumar'9'08/23/2011') --select * from student select [E.No] name [08/23/2011][08/24/2011] [08/25/2011] from ( select [E.No]name date [Hourrs] from student ) x pivot ( Avg([Hourrs]) for date in ([08/23/2011] [08/24/2011] [08/25/2011]) ) p drop table student