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 |
|
Adel99
Starting Member
7 Posts |
 Posted - 2011-11-15 : 23:48:24
|
| I have two tables: Students and Voilations. The Students table's PK is StudentId. The Voilations table contains voliations a student has, which can be of three types ('Absence', 'Late', 'Escape'). The type of voilation is stored as nvarchar in the VoilationType column. So, the Voilations tables is like:VoilationId | StudentId | VoilationType | VoilationDateTime | CommentsI want to get the number of every type of voilation every student has. So, I used:GROUP BY StudentId, VoilationTypeBut this gave me something like this:----------------------------------StudentId | VoilationType | exper1----------------------------------103 | Absence | 2102 | Absence | 1103 | Late | 1103 | Escape | 5102 | Late | 2----------------------------------But I want every type of voilation to be in a different column. Like this:--------------------------------------------------StudentId | AbsenceCases | LateCases | EscapeCases--------------------------------------------------103 | 2 | 1 | 5102 | 1 | 2 | 0 <-- or NULL--------------------------------------------------How to do that? Thank you.Adel, |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-16 : 00:46:46
|
| [code]SELECT s.StudentId,SUM(CASE WHEN v.VoilationType ='Absence' THEN 1 ELSE 0 END) AS [AbsenceCases],SUM(CASE WHEN v.VoilationType ='Late' THEN 1 ELSE 0 END) AS [LateCases],SUM(CASE WHEN v.VoilationType ='Escape' THEN 1 ELSE 0 END) AS [EscapeCases]FROM Students sINNER JOIN Voilations vON v.StudentId = s.StudentId GROUP BY s.StudentId[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Adel99
Starting Member
7 Posts |
Posted - 2011-11-16 : 01:39:03
|
| Thank you so much. It worked perfectly. Thank you, visakh16. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-16 : 01:44:37
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Every Group in a Column