pivot row count P based on my below code in asp.net

Question

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

Answer 1

Hi @RAVI ,
I suggest you could union the result of summing the "P" records.Just like this:

CREATE TABLE #Employee_Attendence  
(  
EmpID bigint,  
AttendenceDate datetime,  
AttendenceStatus char  
)  
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')  
  
  
SELECT DISTINCT AttendenceDate INTO #Dates  
FROM #Employee_Attendence  
ORDER BY AttendenceDate  
  
DECLARE @cols NVARCHAR(4000)  
SELECT @cols = COALESCE(@cols + ',[' + CONVERT(varchar, AttendenceDate, 106)  
+ ']','[' + CONVERT(varchar, AttendenceDate, 106) + ']')  
FROM #Dates  
ORDER BY AttendenceDate  
  
DECLARE @cols1 NVARCHAR(4000)  
SELECT @cols1 = COALESCE(@cols1 + ',cast(sum(IIF([' + CONVERT(varchar, AttendenceDate, 106)  
+ ']=''P'',1,0)) as char(2))','cast(sum(IIF([' + CONVERT(varchar, AttendenceDate, 106) + ']=''P'',1,0)) as char(2))')  
FROM #Dates  
ORDER BY AttendenceDate  
  
DECLARE @qry NVARCHAR(4000)  
SET @qry =  
';WITH CTE AS (  
SELECT EmpID, ' + @cols + ' FROM  
(SELECT EmpID, AttendenceDate, AttendenceStatus  
FROM #Employee_Attendence)p  
PIVOT (MAX(AttendenceStatus) FOR AttendenceDate IN (' + @cols + ')) AS Pvt)  
select cast(empid as char(10)) empid,'+@cols+' from CTE  
union  
select ''Total'','+@cols1+'from CTE'  
EXEC(@qry)  

Result:

Best regards,
Yijing Sun

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our  documentation  to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hello YijingSun-MSFT

Thanks for your code but i want row total as well

Please give me code for row total like this above
Thank you

Answer 3

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '='.

Answer 4

Hello
again error

Incorrect syntax near =
Msg 102, Level 15, State 1, Line 8

can you post complete code it would be helpful

Answer 5

Hello
again error

Incorrect syntax near =
Msg 102, Level 15, State 1, Line 8

can you post complete code it would be helpful