Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
shinelawrence
Starting Member
32 Posts |
Posted - 2013-02-04 : 07:06:36
|
Hi Everyone, I want to take the count of table values. Eg:My Table:Id Name Month Date1 Date2 Date3 Date4 Date51 Lawrence January Present Am Pm Absent Present2 Prem January Present Present Present Present PresentI need to like this result for month wise :ID Name Present Am Pm Absent1 Lawrence 2 1 1 12 Prem 5 0 0 0How to do..Please tell the solution...Thanks In AdvancedLawce |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 07:13:05
|
[code]SELECT Id,Name,[Month],SUM(CASE WHEN Attendance = 'Present' THEN 1 ELSE 0 END) AS Present,SUM(CASE WHEN Attendance = 'Am' THEN 1 ELSE 0 END) AS AM,SUM(CASE WHEN Attendance = 'Pm' THEN 1 ELSE 0 END) AS PM,SUM(CASE WHEN Attendance = 'Absent' THEN 1 ELSE 0 END) AS AbsentFROM(SELECT Id,Name,[Month],DateVal,AttendanceFROM table tUNPIVOT (Attendance FOR DateVal IN ([Date1],[Date2],[Date3],[Date4],[Date5]))u)mGROUP BY Id,Name,[Month][/code]if you cant determine dates before hand use below logic to generate it dynamicallyhttp://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
shinelawrence
Starting Member
32 Posts |
Posted - 2013-02-05 : 00:32:57
|
thank you for your kindly reply....Lawce |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-05 : 00:34:17
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|