Tsql data row/column conversion, Cross report example

 

Normal row-column conversion (in Sanya, Hainan) assume that there is a student quota 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', 7 4) 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 ('lily', 'mat', 84) insert into tb (Name, Subject, Result) values ('lily', 'Physical ', 94) go -- 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 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

 

Cross-Report/row-and-column conversion is a technical point that is often used in actual development work. There are also a lot of questions in the csdn SQL SERVER community. The general requirements are as follows, put similar related columns in the table on the same row. In fact, the requirements are hidden in us.

How to solve the problem. (Row-to-column conversion)
Decomposition steps: the above examples are described.
1. Identify (group by name) on the column instance to be grouped)
2. To construct row-to-column conversion, use the CASE statement and iteration (from (select distinct subject from t) as)
3. Finally, integrate and use print to check whether the static results are correct. Then, execute dynamic SQL (EXEC (@ SQL). If there are many records, the number of Union characters exceeds the maximum limit (8000), and records must be processed in multiple stages. Use a variable to record the operands. When this operand is reached, execute a dynamic SQL statement and initialize @ s.

Go to CSDN: http://topic.csdn.net/u/20071122/13/fc5a7dc8-1f82-4711-8e1b-40932788124a.html