Saturday, August 8, 2009

Use Of Pivot Table in Sqlserver 2005

Display Columns into Rows Using Pivot Table

here is the example that from Attendence table Want to get Yearly Attendence report By Student Wise

select StudentID,
StudentName,
isnull(January,0) January,
isnull(February,0) February,
isnull(March,0) March,
isnull(April,0) April,
isnull(May,0) May,
isnull(June,0) June,
isnull(July,0) July,
isnull(August,0) August,
isnull(September,0) September,
isnull(October,0) October,
isnull(November,0) November,
isnull(December,0) December
from
(
select StudentID, StudentName, DateName(m,AttendenceDate) [Month], Count(*) Total from AttendenceTable
Group by StudentID, DateName(m,AttendenceDate),StudentName
) as source
Pivot
(
sum(Total)
for [month] in (January,February,March,April,May,June,July,August,September, October, November, December)

) as result

No comments: