SQL Server row to column languages, mathematics, physics, Chemistry

Database Query row goto column

1. Original database value

stdname    stdsubject    result Zhang San          Chinese          Zhang San          math          Zhang San          Physical          - John Doe          Chinese          John Doe          Mathematics          John Doe          Physics          the John Doe          chemistry          John Doe          chemistry         

2. To obtain the following table

stdname    Chinese    Mathematics    Physics    Chemistry John Doe                        164  Zhang San                        NULL

3. Implementation methods

--first call row to column functionSelect *  from(SelectStdname,stdsubject,result fromTEST1) asa pivot (sum(Result) forStdsubjectinch(Chinese, mathematics, Physics, Chemistry)) T--The second kind of writing yourselfSelectStdname, Chinese=(Select sum(Result) fromTest1whereStdname=A.stdname andStdsubject= 'language'), Mathematics=(Select sum(Result) fromTest1whereStdname=A.stdname andStdsubject= 'Mathematics'), Physical=(Select sum(Result) fromTest1whereStdname=A.stdname andStdsubject= 'Physical'), Chemical=(Select sum(Result) fromTest1whereStdname=A.stdname andStdsubject= 'Chemical' ) fromTest1 asaGroup  by Stdname