Query help!

Question

CREATE TABLE StuScore 
(   
    ID int IDENTITY(1,1),
    studentId int,
    course varchar(10),
    Score int
);
INSERT INTO StuScore 
Values
(1,'History',80),
(1,'Philosophy',76),
(1,'Math',84),
(2,'History',70),
(2,'Philosophy',66),
(2,'Math',92),
(3,'History',75),
(3,'Philosophy',78),
(3,'Math',80)

Without the ID column, I can easily get the result I want.

select * from StuScore as s
pivot (max(Score) for course in ([History],[Philosophy],[Math])) as t

studentId	History	Philosophy	Math
1	80	76	84
2	70	66	92
3	75	78	80

However, after adding the ID column, the result of the query appears with more rows and a lot of Nulls. Why?

Answer 1

Hi @Harry

A new column changes the query result. This is a normal thing.

Depending on your question, you need to include a 'Group By' in the original query.

How about this query:

select min(id) as id,studentId,max(History) as History,
       max(Philosophy) as Philosophy,max(Math) as Math
from StuScore as s
pivot (max(Score) for course in ([History],[Philosophy],[Math])) as t
group by studentId;

Best regards,

Percy Tang

Answer 2

after adding the ID column, the result of the query appears with more rows

Then I guess you don't "GROUP BY" on the ID column, do you? Please post your complete SQL statement.