Implementation of SQL Server 2000 row-column (horizontal) _mssql

The table structure we use is as follows:

The relationship between the three sheets is:

The existing test data is:

The results we need are:

Copy Code code as follows:

DECLARE @strSQL VARCHAR (8000)
SET @strSQL = ' SELECT t.stuname [name] '
Select @strSQL = @strSQL + ', SUM (case s.sname when ' "+ sname +" ' THEN G.[score] end) [' + sname + '] ' from (select SNA ME from [Subject]) as TMP
SELECT @strSQL = @strSQL + ' from [Score] g,[subject] s, [Student] t WHERE g.sid=s.sid and g.stuid = T.stuid GROUP by t.st UID, T.stuname '
EXEC (@strSQL)

In SQL SERVER 2005, there are already built-in ways to implement this functionality.