SQL Server 2000 row-to-column conversion implementation (horizontal)

Document directory

• The result is:

[Reprinted]

 

 

In some statistical reports, row results are often displayed in columns. Here we use a common score report for each course of students to demonstrate the actual implementation method.

The table structure we use is as follows:

The relationship between the three tables is:

The existing test data is as follows:

The result is:

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 sname from [subject]) as tmpselect @ strsql = @ strsql +' from [score] G, [subject] s, [Student] t where G. SID = S. sid and G. stuid = T. stuid group by T. stuid, T. stuname 'exec (@ strsql)

SQL Server 2005 has a built-in method to implement this function.

 

[I forgot my reprinted address]