hello
This is my code
I want to show total count
Creating table
CREATE TABLE #Employee_Attendence
(
EmpID bigint,
AttendenceDate datetime,
AttendenceStatus char
)
Inserting Values
INSERT #Employee_Attendence
VALUES
(1,'20100601', 'P')
INSERT #Employee_Attendence
VALUES
(1,'20100602', 'P')
INSERT #Employee_Attendence
VALUES
(1,'20100603', 'A')
INSERT #Employee_Attendence
VALUES
(2,'20100601', 'P')
INSERT #Employee_Attendence
VALUES
(2,'20100602', 'A')
INSERT #Employee_Attendence
VALUES
(2,'20100603', 'P')
Getting all distinct dates into a temporary table #Dates
SELECT DISTINCT AttendenceDate INTO #Dates
FROM #Employee_Attendence
ORDER BY AttendenceDate
The number of days will be dynamic. So building
a comma seperated value string from the dates in #Dates
DECLARE @cols NVARCHAR(4000)
SELECT @cols = COALESCE(@cols + ',[' + CONVERT(varchar, AttendenceDate, 106)
+ ']','[' + CONVERT(varchar, AttendenceDate, 106) + ']')
FROM #Dates
ORDER BY AttendenceDate
Building the query with dynamic dates
DECLARE @qry NVARCHAR(4000)
SET @qry =
'SELECT EmpID, ' + @cols + ' FROM
(SELECT EmpID, AttendenceDate, AttendenceStatus
FROM #Employee_Attendence)p
PIVOT (MAX(AttendenceStatus) FOR AttendenceDate IN (' + @cols + ')) AS Pvt'
Executing the query
EXEC(@qry)
Dropping temporary tables
DROP TABLE #Dates
DROP TABLE #Employee_Attendence
Thank you

