SQL Server row and column conversions

SQL column Career2011-04-22 16:13:41

Good thing, turn around, hey. * Normal row and column conversion (Aixinjueluo. Yuk Wah 2007-11-18 in Sanya, Hainan) A Student score table (TB) is provided as follows: Name Subject result Zhang San language 74 Zhang Three mathematics 83 three physics 93 Li four languages 74 John Doe Mathematics 84 John Doe Physical 94*          /-------------------------------------------------------------------------/* Want to become a name Chinese mathematical physics -------------------------------------------Lee 474 84 94 374 83 93 */create table TB (name varchar), Subject varchar (ten), Result int) insert into TB (name, Subject, Result) v Alues (' Zhang San ', ' language ', ' the ') insert into TB (name, Subject, Result) VALUES (' Zhang San ', ' math ', ' the ') insert into TB (name, Subject, Res  Ult) VALUES (' Zhang San ', ' physical ', ', ') insert into TB (name, Subject, Result) VALUES (' John Doe ', ' language ', ' a ') insert into TB (name, Subject , result) VALUES (' John Doe ', ' math ', ' + ') insert into TB (Name, Subject, Result) VALUES (' John Doe ', ' physical ', 94) go--static SQL, meaning Subject only language, Mathematics, physics, this course. Select name Name, max (case subject when ' language ' then result else 0 end) language, Max (case subject when ' math ' then result else 0 end )Math, max (case subject when ' physical ' then result else 0 end) physical from Tbgroup by name/* name Chinese mathematical physics -------------------------------------------Lee 474 84 94 374 83 93 */--dynamic SQL, refers to subject more than language, mathematics, physics, this course. DECLARE @sql varchar (8000) Set @sql = ' Select name as ' + ' name ' Select @sql = @sql + ', max (case Subject when ' + Subject + "then Result else 0 End" [' + Subject + '] ' from (select distinct Subject to TB) as aset @sql = @sql + ' from TB Gro         Up by name ' EXEC (@sql)/* Name Math Physics language-------------------------------------------John Doe 84 94 74 Zhang 383 74*/------------------------------------------------------ -------------*/* plus an average score, the total score of the names Chinese mathematical physics average scores-------------------------       -------------------------------------------------Lee 474 84 94 84.00         252 374 83 93 83.33 250*/--Static SQL, refers to subject only language, mathematics, physics, this course. Select name Name, max (case subject when ' language ' then result else 0 end) language, Max (case subject when ' math ' then result else 0 end ) Math, max (case subject when ' physical ' then result else 0 end) physical, cast (avg (result*1.0) as Decimal (18,2)) average, sum (result) score From Tbgroup by name/* the average score--------------------------of the mathematical physics of the names in Chinese ------------------------------------------------Lee 474 84 94 84.00 25 2 374 83 93 83.33 250*/--Dynamic SQL, refers to subject more than language, mathematics, physics, this course. DECLARE @sql1 varchar (8000) Set @sql1 = ' Select name as ' + ' name ' Select @sql1 = @sql1 + ', max (case Subject when ' + subj ECT + "then Result else 0 End" [' + Subject + '] ' from (select distinct Subject from TB) as aset @sql1 = @sql1 + ', CAs T (avg (result*1.0) as Decimal (18,2)) average, sum (rEsult) Total score from TB GROUP by name ' EXEC (@sql1)/* Name the average total score of the mathematical physical language----          ----------------------------------------------------------------------Lee 484 94 74 84.00 252 photos 383 83.33 250*/drop Table TB------- -----------------------------------------------------------------------------------------------------------/* If the above two tables are exchanged:      That is, the name Chinese mathematics Physics 374 83 93 Lee 474 84 94 want to become name Subject Result----------------------------John Doe language  74 John Doe Mathematics 84 John Doe Physics 94 three languages 74 Zhang three mathematics 83 Zhang three Physics 93*/create table TB1 (name varchar (10), language int, math int, physical int) insert into TB1 (name, language, mathematics, physics) VALUES (' Zhang San ', 74,83,93) Insert int   o tb1 (name, language, mathematics, physics) VALUES (' John Doe ', 74,84,94) SELECT * FROM (select name as name, Subject = ' language ', Result = language from tb1 UNION ALL select name as NAme, Subject = ' math ', result = Math from tb1 UNION ALL select name as name, Subject = ' physical ', result = physical from tb1) Torder By name, case Subject when ' language ' then 1 when ' math ' then 2 when ' physical ' then 3 when ' total ' then 4 end---------------------------- ----------------------------------------/* Plus an average score, total score name Subject Result----------------------- --------------John Doe Language 74.00 John Doe Mathematics 84.00 John Doe physical 94.00 Li Shiping evenly 84.00 John Doe Total      Divided into 252.00 three languages 74.00 Zhang three mathematics 83.00 three physics 93.00 Zhang three average divided 83.33 Zhang three total 250.00*/select * FROM (select name as name, Subject = ' language ', Result = language from tb1 union ALL select name as name, Su bject = ' math ', result = Math from tb1 UNION ALL select name as name, Subject = ' physical ', result = physical from tb1 UNION ALL Sele CT name as name, Subject = ' average score ', Result = cast ((language + math + physics) *1.0/3 as Decimal (18,2)) from TB1 union ALL select name as N Ame, Subject = ' total score ', REsult = language + math + physics from TB1) Torder by name, Case Subject when ' language ' then 1 when ' math ' then 2 when ' physical ' then 3 when ' average ' Then 4 when ' total score ' then 5 enddrop table Tb1

SQL Server row and column conversions