SQL column-to-row

Document directory

• SQL column-to-row

SQL column forwarding 16:13:41

Good stuff, turn it around, hey. * Normal row/column Conversion. yanghua in Sanya, Hainan) assume that there is a student renewal table (tb) as follows: name Subject Result Zhang San Language 74 Zhang San mathematics 83 Zhang San physics 93 Li Si language 74 Li Si mathematics 84 Li Si physics 94 */else/* want to change to Name Chinese mathematics physics ---------- ----------- --------- ----------- Li Si 74 84 94 zhang San 74 83 93 */create table tb (Name varchar (10 ), subject varchar (10), Result int) insert into tb (Name, Subject, Result) values ('zhang san ', 'China', 74) insert into tb (Name, Subject, Result) values ('zhang san', 'mat', 83) insert into tb (Name, Subject, Result) values ('zhang san', 'Physical ', 93) insert into tb (Name, Subject, Result) values ('Li si', 'China', 74) insert into tb (Name, subject, Result) values ('Li si', 'mat', 84) insert into tb (Name, Subject, Result) values ('Li si', 'Physical ', 94) go-static SQL indicates that subject has only three courses: Chinese, mathematics, and physics. Select name, max (case subject when 'then result else 0 end) language, max (case subject when 'mate' then result else 0 end) mathematics, max (case subject when 'physical 'then result else 0 end) physical from tbgroup by name/* name Chinese mathematics physics ---------- ----------- Li Si 74 84 94 Zhang San 74 83 93 */-- dynamic SQL, subject is not only a course of Chinese, mathematics, and physics. 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 from tb) as aset @ SQL = @ SQL + 'from tb group by name' exec (@ SQL) /* Name: Mathematical Physics language ---------- ----------- --------------- Li Si 84 94 74 Zhang San 83 93 74 */-------------------------------------------------- -----------------/* Add an average score. The total score is the average score of Chinese Mathematics and Physics. ----------------------- Li Si 74 84 94 84.00 Zhang 3 74 83 252 83.33 */-- Static SQL, subject only has three courses: Chinese, mathematics, and physics. Select name, max (case subject when 'then result else 0 end) language, max (case subject when 'mate' then result else 0 end) mathematics, max (case subject when 'physical 'then result else 0 end) Physical, cast (avg (result * 1.0) as decimal () average score, sum (result) total score from tbgroup by name/* name average score of Chinese mathematics physics total score ---------- ----------- --------------- ------------------ ------------- Li Si 74 84 94 84.00 252 Zhang San 74 83 93 83.33 25 0 */-- dynamic SQL, refers to subject not only Chinese, Mathematics, Physics these three courses. Declare @ sql1 varchar (8000) set @ sql1 = 'select Name as '+ 'name' select @ sql1 = @ sql1 + ', max (case Subject when ''' + Subject + ''' then Result else 0 end) ['+ Subject +'] 'from (select distinct Subject from tb) as aset @ sql1 = @ sql1 + ', cast (avg (result * 1.0) as decimal () average score, sum (result) total from tb group by name' exec (@ sql1) /* Name Mathematical Physics average score ---------- ----------- ---------------------- ----------- Li Si 84 94 74 84.00 252 Zhang San 83 93 74 83.33 250 */drop table tb else/* If the above two tables change to each other below: that is, Name Chinese mathematics physics Zhang San 74 83 93 Li Si 74 84 94 want to become Name Subject Result ---------- ------------- Li Si language 74 Li Si mathematics 84 Li Si physics 94 Zhang San Language 74 Zhang San mathematics 83 Zhang San physics 93 * /create table tb1 (Name: varchar (10 ), language int, mathematics int, physical int) insert into tb1 (name, language, mathematics, physics) values ('zhang san', 93) insert into tb1 (name, language, mathematics, Physics) values ('Li si', 94) select * from (select Name as Name, Subject = 'China ', result = language from tb1 union all select Name as Name, Subject = 'mate', Result = mathematics from tb1 union all select Name as Name, Subject = 'physical ', result = physical from tb1) torder by name, case Subject when 'chine' then 1 when' math 'then 2 when' physical 'then 3 when' total score 'then 4 end hour/* add an average score, total score Name Subject Result ---------- ------- ------------------ Li Si language 74.00 Li Si mathematics 84.00 Li Si physics 94.00 Li Si average score 84.00 Li Si total score 252.00 Zhang San Language 74.00 Zhang San mathematics 83.00 Zhang San physics 93.00 Zhang San average score 83.33 Zhang San total score 250.00 */select * from (select Name as Name, subject = 'China', Result = Chinese from tb1 union all select Name as Name, Subject = 'mate', Result = mathematics from tb1 union all select Name as Name, subject = 'physical ', Result = physical from tb1 union all select Name as Name, Subject = 'average', Result = cast (Language + mathematics + physics) * 1.0/3 as decimal () from tb1 union all select Name as name, Subject = 'Total', Result = language + mathematics + physics from tb1) torder by Name, case Subject when 'chine' then 1 when' math 'then 2 when' then 3 when' average score 'then 4 when' total score 'then 5 enddrop table tb1