2011-02-05
Sql PIVOT Sample
SELECT a.AttendancesID,a.EmployeeNo,MONTH(a.AttendancesDate) As AttMonth
FROM Attendances a
WHERE YEAR(a.AttendancesDate) = '2010'
GROUP BY a.AttendancesID, a.EmployeeNo, MONTH(a.AttendancesDate)
ORDER BY a.AttendancesID,a.EmployeeNo,AttMonth
SELECT *
FROM (
SELECT a.EmployeeNo,
CASE MONTH(a.AttendancesDate)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END AS AttMonth,a.AttendancesID AS AttCount
FROM Attendances a
WHERE YEAR(a.AttendancesDate) = '2010'
GROUP BY a.EmployeeNo, MONTH(a.AttendancesDate), a.AttendancesID
) DataTable
PIVOT(
COUNT(AttCount)
FOR AttMonth
IN ([January], [February], [March], [April], [May], [June], [July],[August], [September], [October], [November], [December])
)
PivotTable
ORDER BY EmployeeNo
Subscribe to:
Post Comments (Atom)
SQL Query Stuff
1 . By using this query can search any string contain within stored procedure, function or view SELECT object_name(id) FROM sys.sysc...
-
Code Output DECLARE @count INT DECLARE @ct INT DECLARE @print varchar(10) SELECT @count =10 WHILE @count > 0 BEGIN SELECT @pr...
-
****> **add name="ConToSOLDB" connectionString="Data Source=db-svr\MSSQL2008; Database=SOLV3; User Id=sa; password=...
-
In the Following code i am going to generate a Grid view with dynamic check box columns. as u can seen on the picture...
1 comment:
Pivoting data in SQL via PivotGrid Control
Post a Comment