Conversion between SQL Server rows and columns

SQL Server converts rows to columns: for example, if a table contains only a small amount of data, you can use static SQL statements such as selectstuName and max (caseCoursewhen language thenscoreelse0end) as language, max (caseCoursewhen math thenscoreelse0end) as math, max (caseCoursewhen

SQL Server transfers rows to columns, as shown in the following table: When a table contains only a small amount of data, you can use static SQL statements such as select stuName, max (case Course when 'then score else 0 end) as language, max (case Course when 'mate' then score else 0 end) as mathematics, max (case Course when'

SQL Server converts rows to columns:

See the following table:

When a table contains only a small amount of data, you can use static SQL statements.

For example:

Select stuName,
Max (case Course when 'China' then score else 0 end) as language,
Max (case Course when 'mate' then score else 0 end) as mathematics,
Max (case Course when 'then score else 0 end) as English,
Max (case Course when 'physical 'then score else 0 end) as physical,
Sum (score) as 'Total'
From Student group by stuName

When a data table contains a large amount of data, dynamic SQL statements must be used.

Follow these steps:

Declare @ name varchar (8000) -- declare a variable
Set @ name = 'select stuname'
Select @ name = @ name + ', sum (case Course when ''' + Course + ''' then score else 0 end) [' + Course + ']'
From (select distinct Course from Student) as
Set @ name = @ name + 'from Student group by stuname'
Exec (@ name)

The distinct keyword is used to filter out redundant duplicate records and retain only one record, but it is often used to return the number of records that do not repeat, instead of returning all values that do not record duplication.

The above two results are the same, and the results are as follows: