question

RAVI-6930 avatar image
0 Votes"
RAVI-6930 asked YijingSun-MSFT commented

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

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

dotnet-aspnet-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YijingSun-MSFT avatar image
0 Votes"
YijingSun-MSFT answered

Hi @RAVI-6930 ,
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:
115289-capture2.png

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.


capture2.png (2.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RAVI-6930 avatar image
0 Votes"
RAVI-6930 answered

Hello YijingSun-MSFT

Thanks for your code but i want row total as well

115519-image.png



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


image.png (7.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RAVI-6930 avatar image
0 Votes"
RAVI-6930 answered YijingSun-MSFT commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RAVI-6930,
You could double click the error and it will jump to the error lines. About rowTotal,you could do like this:

  DECLARE @RowTotal NVARCHAR(4000)
  SELECT @RowTotal = COALESCE(@RowTotal + ',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
    
  select cast(empid as char(10)) empid,'+@cols+', SUM('+@RowTotal+') from CTE 
  union
  select ''Total'','+@cols1+',SUM('+@RowTotal+') from CTE'

Best regards,
Yijing Sun

0 Votes 0 ·
RAVI-6930 avatar image
0 Votes"
RAVI-6930 answered

Hello
again error

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

can you post complete code it would be helpful

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RAVI-6930 avatar image
0 Votes"
RAVI-6930 answered YijingSun-MSFT commented

Hello
again error

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

can you post complete code it would be helpful

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @RAVI-6930,
These two replayed codes are all codes.And your error lines don't same with me. You could double click the error according the error. Or you could post your codes to us.
Best regards,
Yijing Sun

0 Votes 0 ·