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

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.