How to do rows total in pivot table?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

I want to of Present,Absent,CL,H

Date     1    2      3      4   5    P  CL  A
Emp1     P    P      P      P   CL   4  1   0
Emp2     CL   SL     P      P   H    2  2   0
Emp3     A    A      A      P   H    1  0   3

PLease help?

What I have tried:

i tried to convert column into rows now i want to of A,P,CL

ALTER Procedure [dbo].[Pivot_Attendance2]
--@StartDate Date,
--@Enddate   Date

As  
Begin 

SELECT DISTINCT ReportingDate INTO #Datess
FROM EmployeesAttendance
--WHERE     (ReportingDate BETWEEN @StartDate AND @Enddate)
ORDER BY ReportingDate

DECLARE @cols NVARCHAR(4000)
SELECT  @cols = COALESCE(@cols + ',[' + CONVERT(varchar, DATEPART(DAY, ReportingDate), 112)
				+ ']','[' + CONVERT(varchar,DATEPART(DAY, ReportingDate), 112) + ']')
FROM    #Datess
ORDER BY ReportingDate

DECLARE  @qry NVARCHAR(4000) =
N'SELECT * 
FROM (SELECT     EmpID,  CASE WHEN [Days] IS null AND EXISTS
                          (SELECT     1
                            FROM          EmpApplication
                            WHERE      EmployeesAttendance.Empid = EmpApplication.Empid AND 
                            (ReportingDate >= LeavFrom AND ReportingDate <= LeavTo))
                             THEN ''CL''
                             WHEN [Days] IS null AND
                       EXISTS
                          (SELECT     1
                            FROM          Holidays
                            WHERE      ReportingDate = HolidayDate) 
                            THEN ''H'' 
                            WHEN [Days] IS null THEN ''A'' WHEN [Days] = 1 THEN ''P'' END AS Status,
         DATEPART(DAY, ReportingDate)as DDate FROM EmployeesAttendance )emp


PIVOT (MAX(Status) FOR DDate IN (' + @cols + ')) AS stat 
            ORDER BY RNO_ROWTOTAL,''p'';

'
-- Executing the query

EXEC(@qry)
 
END

Posted 26-Jan-19 4:29am

akhter86

Updated 30-Jan-19 2:50am

Add a Solution

Comments

CHill60 30-Jan-19 6:34am

Your question does not make any sense. Try re-writing it - use the Improve question link to update this post

akhter86 30-Jan-19 6:36am

i Have data in column then i transfer it into rows,now i want row total as i Mentioned above ?
P CL A

2 solutions

Solution 1

You actually typed "to of" … now I understand that you mean "total of".

I assume you mean that you want to see A+P+CL as a new column in your results

Try something like this (untested)

SQL

SET qry = 'SELECT *, A+P+CL FROM (' & qry & ') as x'
EXEC(@qry)

Posted 30-Jan-19 0:49am

CHill60

Comments

akhter86 30-Jan-19 21:51pm

Sorry for mistake ,,but your provided how to use , i tried us integrate with query but not giving resultMsg 102, Level 15, State 1, Line 29
Incorrect syntax near ' & @qry & '

CHill60 31-Jan-19 1:56am

I've used & .. it should be +

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santosh kumar Pithani · Accepted Answer · 2019-01-30T02:51:00

Solution 2

SQL

DECLARE  @qry NVARCHAR(4000);


SET @qry=N';WITH CTE AS(
                SELECT EmpID,
				       CASE 
					      WHEN [Days] IS null AND
						    EXISTS
                            (SELECT  1 FROM EmpApplication
                              WHERE      EmployeesAttendance.Empid = EmpApplication.Empid AND 
                              (ReportingDate >= LeavFrom AND ReportingDate <= LeavTo))  THEN ''CL''
                          WHEN [Days] IS null AND
                               EXISTS
                               ( SELECT  1  FROM Holidays WHERE  ReportingDate = HolidayDate)  THEN ''H'' 
						  WHEN [Days] IS null  THEN ''A'' WHEN [Days] = 1 THEN ''P'' 
								   END AS Status,
                       DATEPART(DAY, ReportingDate)as DDate FROM EmployeesAttendance 
					   )


SELECT * FROM (
             SELECT EmpID,Status,Status AS New_status,DDate FROM CTE 
			  ) AS Emp
PIVOT (MAX(Status) FOR DDate IN (' + @cols + ')) AS stat 

PIVOT(COUNT(New_status) FOR New_status IN ([P],[CL],[A])) AS Pvt;'
-- Executing the query

EXEC(@qry)

Posted 30-Jan-19 2:51am

Santosh kumar Pithani

Updated 30-Jan-19 18:22pm

v2

Comments

akhter86 30-Jan-19 21:47pm

Dear @Santosh Kumar here is error coming
(8 row(s) affected)
Msg 207, Level 16, State 1, Line 19
Invalid column name 'Status'.
Msg 207, Level 16, State 1, Line 19
Invalid column name 'Status'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'RNO_ROWTOTAL'.

Santosh kumar Pithani 30-Jan-19 23:54pm

Hello @akhter , already you have used 'Status' column for pivot so you may not use for second time same column for pivot.Take extra columns as "Status AS New_Status"
use that for second pivot.This "RNO_ROWTOTAL" column not existed in your derived table.

akhter86 31-Jan-19 22:00pm

i used
PIVOT(COUNT(Status) FOR Status IN ([P],[CL],[A])) AS New_Status

ORDER BY RNO_ROWTOTAL,''p'' ;
but same issue coming..please guide

Santosh kumar Pithani 31-Jan-19 23:43pm

Post your error msg;have you executed solution query?I clearly mentioned two columns("Status,Status AS New_status") and how to use them for pivot(PIVOT (MAX(Status) FOR DDate IN (' + @cols + ')) AS stat

PIVOT(COUNT(New_status) FOR New_status IN ([P],[CL],[A])) AS Pvt;")
.Still your least to understand its your problem.

akhter86 1-Feb-19 1:56am

This error is coming...
(62 row(s) affected)
Msg 8156, Level 16, State 1, Line 21
The column '27' was specified multiple times for 'stat'.
Msg 8156, Level 16, State 1, Line 23
The column '27' was specified multiple times for 'Pvt'.

Santosh kumar Pithani 1-Feb-19 3:29am

I think day(27) is repeating more than one times check and make sure with
query "select @cols"

akhter86 1-Feb-19 5:45am

Yes it will report for each employee in a day.

Santosh kumar Pithani 1-Feb-19 8:18am

If it is correct then mark as solution.

Santosh kumar Pithani 31-Jan-19 0:22am

I have updated solution check and let me know!