SQL Server group and inverse

Question

I'm stuck with an easy SQL Server thing for my own personal project.

I have a table X:

| Name       | Lecture     | Points       |
|:-----------|------------:|:------------:|
| John       |        Math |     2     
| John       |        Bio  |     5    
| Tom        |     Physics |     8     
| Tom        |        Math |     2      
| Bob        |     Physics |     1    
| Bob        |        Bio  |     6

And I want to group by Name and to put all points I one row for each person:

| Name       | Math  | Bio  | Physics |
|:-----------|------:|:----:|:-------:|
| John       |   2   |  5   |   NULL     
| Tom        |   2   | NULL |     8    
| Bob        |  NULL |  6   |     1

I tried doing this:

SELECT Name, ? AS Math, ? AS Bio, ? AS Physics
FROM X 
GROUP BY Name

but I don't know what to put instead of "?". How can I do that ?

You need to do either a PIVOT or a crosstab (also known as conditional aggregation). — Sean Lange, Feb 12 '16 at 16:25
Yes a PIVOT will do nicely. For something simple, as in the example, above it will be pretty straight forward. However, if your pivot columns are unknown - you have ART, SPANISH, PE, etc - and more can be created, you will want to look at creating a dynamic PIVOT. Both topics are covered well on stack exchange — Steve Mangiameli, Feb 12 '16 at 17:31

score 0 · Answer 1 · answered Feb 12 '16 at 17:57

You need a pivot table and you need to know the values, unless you want to use dynamic sql (not recommended unless absolutely necessary):

SELECT Name,
    ISNULL([Math], 0) as [Math],
    ISNULL([Bio], 0) as [Bio],
    ISNULL([Physics], 0) as [Physics]
FROM
(
    SELECT Name, Lecture, SUM(Points)
    FROM Table X
    GROUP BY Name, Lecture
) as t1
PIVOT (SUM([Points]) for [Lecture] in ([Math], [Bio], [Physics])) as t2

SQL Server group and inverse

1 Answers1