CODEWITHSUNDEEP
sqlsql-server
Archaimot
Archaimot

Reputation: 93

Separate Differing values into columns

I have a dataset that looks something like

StudentName | Grading Type | AssignmentName | Grade Given
John Doe    | Exam         | Exam 1         | 90.2
John Doe    | Exam         | Midterm        | 87.0
John Doe    | Homework     | Week 1         | 75.0
John Doe    | Homework     | Week 2         | 100.0
Jane Doe    | Homework     | Week 1         | 100.0
...           ...            ...              ...

And I want to get the average of the grade given per grading type as columns for each student, how do I go about this? See below for sample/desired output

StudentName | Exam | Homework 
John Doe      89.0   88.7
Jane Doe      77.0   100.0

Realize this is a dramatic oversimplification of the dataset I'm talking about and a case statement would involve upwards of 60 cases, So I'm looking for something that actually partitions it into new columns based on the controlled type. I do understand the avg function --I'm hoping there's some function whose intricacies I have missed. Any and all help would be appreciated.

Upvotes: 1

Views: 44

Answers (3)

SQLBadPanda
SQLBadPanda

Reputation: 635

You can use a PIVOT command to do this.

Something like this should achieve what you want...

DROP TABLE IF EXISTS dbo.Schoolwork;
CREATE TABLE dbo.Schoolwork
(StudentName varchar(128), GradingType varchar(30), AssignmentName varchar(30), GradeGiven decimal(4,1));
INSERT dbo.Schoolwork (StudentName,GradingType,AssignmentName,GradeGiven)
VALUES 
('John Doe','Exam','Exam 1',90.2 ),
('John Doe','Exam','Midterm',87.0 ),
('Jane Doe','Exam','Exam 1',77.0 ),
('John Doe','Homework','Week 1',75.0 ),
('John Doe','Homework','Week 2',100.0),
('Jane Doe','Homework','Week 1',100.0);

SELECT * 
FROM (
        SELECT s.StudentName,
               s.GradingType,
               s.GradeGiven 
        FROM dbo.Schoolwork AS s
      ) AS src
      PIVOT (avg(src.GradeGiven) FOR GradingType IN ([Exam],[Homework])
      ) AS pvt;

(I took the liberty of adding Jane Doe's exam result to the test data).

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

using conditional aggregation:

select 
    StudentName
  , Exam = avg(case when GradingType = 'Exam' then GradeGiven end)
  , Homework = avg(case when GradingType = 'Homework' then GradeGiven end)
from t
group by StudentName

rextester demo: http://rextester.com/VMB21085

returns:

+-------------+-----------+------------+
| StudentName |   Exam    |  Homework  |
+-------------+-----------+------------+
| Jane Doe    | NULL      | 100.000000 |
| John Doe    | 88.600000 | 87.500000  |
+-------------+-----------+------------+

For a dynamic pivot():

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
  select @cols = stuff((
    select distinct 
      ', ' + quotename(isnull(nullif(GradingType,''),'unknown'))
      from t 
      order by 1
      for xml path (''), type).value('(./text())[1]','nvarchar(max)')
    ,1,2,'')
select  @sql ='
 select StudentName, ' + @cols +'
  from  (
    select StudentName, GradingType, GradeGiven
      from t
    ) as t
 pivot (avg(GradeGiven) for GradingType in (' + @cols +')) p'
select @sql
exec(@sql);

generates the following code: 

select StudentName, [Exam], [Homework]
  from  (
    select StudentName, GradingType, GradeGiven
      from t
    ) as t
 pivot (avg(GradeGiven) for GradingType in ([Exam], [Homework])) p

and returns:

+-------------+-----------+------------+
| StudentName |   Exam    |  Homework  |
+-------------+-----------+------------+
| Jane Doe    | NULL      | 100.000000 |
| John Doe    | 88.600000 | 87.500000  |
+-------------+-----------+------------+

Upvotes: 3

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use it.

SELECT * FROM
    (SELECT StudentName, [Grading Type], [Grade Given] FROM MyTable) SRC
    PIVOT( AVG( [Grade Given] ) FOR [Grading Type] IN ([Exam],[Homework])) PVT

Upvotes: 1

Related Questions