Normal row and column conversion (cross table, horizontal table variable list)

SQL Code

/* Title: Normal row and column conversion (version 2.0) Author: Aixinjueluo Yu Hua (18 Years wind and rain, keep the iceberg Snow lotus blossom) Time: 2008-03-09 location: Guangdong Shenzhen: General row and column conversion (version 1.0) only for SQL Server 2000 provides static and dynamic writing, and version 2.0 increases the syntax for SQL Server 2005. Question: Suppose there is a student score table (TB) as follows: Name Course score John Language 74 three mathematics 83 three physics 93 Li four languages 74 Dick Mathematics 84 Dick Physics 94 want to become (get the following result): Name Language Mathematics physics------------ ----Lee 474 84 94 374-------------------* * CREATE TABLE TB (name varchar (10), course varchar (10), score int) Inse RT into TB values (' John ', ' language ', ') ' INSERT into TB values (' John ', ' Math ', ') insert into TB values (' John ', ' Physics ', ' INSERT into TB values (' Dick ', ' language ', ') insert into TB values (' Dick ', ' math ', ') insert INTO t b values (' Dick ', ' physical ', ') go--SQL SERVER 2000 static SQL, refers to the course only language, mathematics, physics, the three courses. Select name as name, Max (case course when ' language ' then score else 0 end) language, Max (when ' mathematics ' then score else 0 end) Math, max (case course when ' physical ' then score else 0 end) physical from TB group by name--SQL SERVER 2000 dynamic SQL, refers to courses more than language, mathematics, physics, the three courses. DECLARE @sql varchar (in the same below) (8Set @sql = ' Select name ' Select @sql = @sql + ', max (case course when ' + course + ' ' then score else 0 end) [' + Course + '] ' From (select DISTINCT course from TB) as a set @sql = @sql + ' from TB GROUP by name ' EXEC (@sql)--SQL SERVER 2005 Static sq L SELECT * FROM TB a pivot (max (fractions) for course in (language, math, physics)) B-SQL SERVER 2005 dynamic SQL. DECLARE @sql varchar (8000) Select @sql = IsNull (@sql + '],[', ') + course from TB GROUP BY course set @sql = ' [' + @sq L + '] ' EXEC (' select * from TB ') a pivot (max (score) for course in (' + @sql + '))------------------ ---------------/* Problem: On the basis of the above results, add the average points, total score, get the following results: The name of the mathematical physics of the average score--------------------------Lee 474 84 94 84.00 252 Zhang Three 83.33-SQL SERVER 2000 static SQL. Select name, max (case course when ' language ' then score else 0 end) language, Max (case course when ' math ' then fraction else 0 end) mathematics, Max ( Case course when ' physical ' then score else 0 end ' physics, CAST (AVG (Fractional * 1.0) as decimal (18, 2)) Average score, sum (score) total score fromTB GROUP BY name-SQL SERVER 2000 dynamic SQL. DECLARE @sql varchar (8000) Set @sql = ' Select name ' Select @sql = @sql + ', max (case course when ' + course + ' ' then fractional El SE 0 end) [' + Course + '] ' from (select DISTINCT course from TB) as a set @sql = @sql + ', cast (avg. *1.0) as decimal (18,2) ) Average score, sum (score) total score from TB GROUP by name ' EXEC (@sql)--SQL SERVER 2005 static SQL. Select M. *, N. Average score, N. Total score from (SELECT * FROM (SELECT * to TB) a pivot (max (fractions) for course in (Chinese, maths, physics)) m, (Selec T name, CAST (avg. * 1.0) as decimal (18, 2)) Average score, sum (score) total score from TB Group by name) n where M. name = N. Name--SQL SER VER 2005 Dynamic SQL. DECLARE @sql varchar (8000) Select @sql = IsNull (@sql + ', ', ') + course from TB GROUP by course exec (' Select m.*, N. Average score, N. Total from (SELECT * from TB) a pivot (max (score) for course in (' + @sql + ')) m, (select name, cast (AV G (fractional *1.0) as decimal (18,2)) Average score, sum (score) total from TB Group by name N where m. Name = N. name ') drop table TB----------------- - -- ----------------/* Problem: If the above two tables change each other: the table structure and data are: the name of the Chinese mathematical physics Zhang 374 83 93 Li 474 84 94 want to become (get the following results): Name Course score------------dick Language 74 Dick Mathematics 84 Dick Physics 94 three languages 74 three mathematics 83 three Physics--------------* * CREATE TABLE TB (name varchar