In SQL server 2008, I have below table Score. I want to show Score1 and Score2 together for same student. Name and Email combined uniquely identifies a student (Name or Email may be missing too, like Jack and Maya@moon.com). The expected output shown as T_Combined.
Score
Name Email Score1 Score2
John 'John@pluto.com' 75
Peter 'Peter@pluto.com' 34
Nina 'Nina@pluto.com' 45
Joseph 'Joseph@pluto.com' 76
Tom 'Tom@pluto.com' 43
Sam 'Sam@pluto.com' 76
Nancy 'Nancy@pluto.com' 12
Tina 'Tina@pluto.com' 56
John 'John@mars.com' 98
Peter 'Peter@mars.com' 12
Nina 'Nina@mars.com' 45
Joseph 'Joseph@mars.com' 87
Tom 'Tom@mars.com' 67
Sam 'Sam@mars.com' 99
Nancy 'Nancy@mars.com' 33
Tina 'Tina@mars.com' 23
John 'John@pluto.com' 86
Peter 'Peter@pluto.com' 56
Nina 'Nina@pluto.com' 98
Joseph 'Joseph@pluto.com' 78
Tom 'Tom@pluto.com' 12
Sam 'Sam@pluto.com' 45
Nancy 'Nancy@pluto.com' 76
Tina 'Tina@pluto.com' 78
John 'John@mars.com' 98
Peter 'Peter@mars.com' 45
Nina 'Nina@mars.com' 76
Joseph 'Joseph@mars.com' 12
Tom 'Tom@mars.com' 84
Sam 'Sam@mars.com' 27
Nancy 'Nancy@mars.com' 54
Tina 'Tina@mars.com' 50
Jack 10
'Maya@moon.com' 20
T_Combined
Name Email Score1 Score2
John 'John@pluto.com' 86
Peter 'Peter@pluto.com' 56
Nina 'Nina@pluto.com' 98
Joseph 'Joseph@pluto.com' 78
Tom 'Tom@pluto.com' 43 12
Sam 'Sam@pluto.com' 76 45
Nancy 'Nancy@pluto.com' 12
Tina 'Tina@pluto.com' 56
John 'John@mars.com' 98
Peter 'Peter@mars.com' 12
Nina 'Nina@mars.com' 45 76
Joseph 'Joseph@mars.com' 87 12
Tom 'Tom@mars.com' 67 84
Sam 'Sam@mars.com' 99 27
Nancy 'Nancy@mars.com' 33 54
Tina 'Tina@mars.com' 23 50
Jack 10
'Maya@moon.com' 20
Many thanks
Create table Score (Name varchar(20),Email varchar(20),Score1 int,Score2 int)
insert into Score (Name,Email,Score1)values('John','John@pluto.com',75) insert into Score (Name,Email,Score1)values('Peter','Peter@pluto.com',34) insert into Score (Name,Email,Score1)values('Nina','Nina@pluto.com',45) insert into Score (Name,Email,Score1)values('Joseph','Joseph@pluto.com',76) insert into Score (Name,Email,Score1)values('Tom','Tom@pluto.com',43) insert into Score (Name,Email,Score1)values('Sam','Sam@pluto.com',76) insert into Score (Name,Email,Score1)values('Nancy','Nancy@pluto.com',12) insert into Score (Name,Email,Score1)values('Tina','Tina@pluto.com',56) insert into Score (Name,Email,Score1)values('John','John@mars.com',98) insert into Score (Name,Email,Score1)values('Peter','Peter@mars.com',12) insert into Score (Name,Email,Score1)values('Nina','Nina@mars.com',45) insert into Score (Name,Email,Score1)values('Joseph','Joseph@mars.com',87) insert into Score (Name,Email,Score1)values('Tom','Tom@mars.com',67) insert into Score (Name,Email,Score1)values('Sam','Sam@mars.com',99) insert into Score (Name,Email,Score1)values('Nancy','Nancy@mars.com',33) insert into Score (Name,Email,Score1)values('Tina','Tina@mars.com',23)
insert into Score (Name,Email,Score2)values('John','John@pluto.com',86) insert into Score (Name,Email,Score2)values('Peter','Peter@pluto.com',56) insert into Score (Name,Email,Score2)values('Nina','Nina@pluto.com',98) insert into Score (Name,Email,Score2)values('Joseph','Joseph@pluto.com',78) insert into Score (Name,Email,Score2)values('Tom','Tom@pluto.com',12) insert into Score (Name,Email,Score2)values('Sam','Sam@pluto.com',45) insert into Score (Name,Email,Score2)values('Nancy','Nancy@pluto.com',76) insert into Score (Name,Email,Score2)values('Tina','Tina@pluto.com',78) insert into Score (Name,Email,Score2)values('John','John@mars.com',98) insert into Score (Name,Email,Score2)values('Peter','Peter@mars.com',45) insert into Score (Name,Email,Score2)values('Nina','Nina@mars.com',76) insert into Score (Name,Email,Score2)values('Joseph','Joseph@mars.com',12) insert into Score (Name,Email,Score2)values('Tom','Tom@mars.com',84) insert into Score (Name,Email,Score2)values('Sam','Sam@mars.com',27) insert into Score (Name,Email,Score2)values('Nancy','Nancy@mars.com',54) insert into Score (Name,Email,Score2)values('Tina','Tina@mars.com',50)
insert into Score (Name,Score1)values('Jack',10) insert into Score (Email,Score2)values('Maya@moon.com',20)
select Name, Email, isnull(SUM(Score1),'') as Score1, isnull(SUM(Score2),'') as Score2
from Score
group by Name, Email