-- Row and column conversion of classic SQL statements
-- Http://www.programbbs.com/doc/4885.htm/##:normal row-column conversion (Version 2.0) Description: normal row-column conversion (version 1.0) only provides static and dynamic writing for SQL Server 2000, and Version 2.0 adds SQL Server 2005. Question: Suppose there is a student's score table (TB) as follows: Name course score Zhang San Language 74 Zhang San mathematics 83 Zhang San physics 93 Li Si language 74 Li Si mathematics 84 Li Si physics 94 want to change (the following result is obtained): name Chinese mathematics physics ---- Li Si 74 84 94 Zhang San 74 83 93 ------------------- */create table Tb (name varchar (10), course varchar (10), score INT) insert into TB values ('zhang san', 'China', 74) insert into TB values ('zhang san', 'mat', 83) insert into TB values ('zhang san ', 'Physical ', 93) insert into TB values ('Li si', 'China', 74) insert into TB values ('Li si', 'Mat', 84) insert into TB values ('lily', 'Physical ', 94) Go -- SQL Server 2000 static SQL, only Chinese, mathematics, and physics courses are required. (The same as below) Select name as name, max (Case course when 'chine' then score else 0 end) language, max (Case course when 'mate' then score else 0 end) mathematics, max (Case course when 'physical 'Then score else 0 end) Physical from tbgroup by name -- SQL Server 2000 dynamic SQL, refers to the course more than three courses: Chinese, mathematics, and physics. (The same as below) Declare @ SQL varchar (8000) set @ SQL = 'select name' select @ SQL = @ SQL + ', max (Case course when''' + course + ''' then score else 0 end) ['+ course +'] 'from (select distinct course from TB) as Aset @ SQL = @ SQL + 'from TB group by name' exec (@ SQL) -- SQL Server 2005 static SQL. Select * from (select * from TB) A between (max (score) for course in (language, mathematics, physics) B -- SQL Server 2005 dynamic SQL. Declare @ SQL varchar (8000) Select @ SQL = isnull (@ SQL + ',','') + course from TB group by course exec ('select * from (select * from TB) a round (max (score) for course in ('+ @ SQL + ')) B ') ---------------------------------/* problem: add the average score based on the above results. The total score is obtained as follows: name: average score of Chinese Mathematics and Physics ---- Li Si 74 84 94 84.00 252 Zhang San 74 83 93 83.33 250 */-- SQL Server 2000 static SQL. Select name, max (Case course when 'chine' then score else 0 end) language, -- else null max (Case course when 'mate' then score else 0 end) mathematics, max (Case course when 'physical 'Then score else 0 end) Physical, cast (AVG (score * 1.0) as decimal () average score, sum (score) total from tbgroup by name/* with T (
Select 1
Union
Select 2
Union
Select-1
Union
Select null
)
Select min (a) from t -- the result is-1 select max (a) from t -- the result is 2 */-- SQL Server 2000 dynamic SQL. Declare @ SQL varchar (8000) set @ SQL = 'select name' select @ SQL = @ SQL + ', max (Case course when''' + course + ''' then score else 0 end) ['+ course +'] 'from (select distinct course from TB) as Aset @ SQL = @ SQL + ', cast (AVG (score * 1.0) as decimal () average score, sum (score) total from TB group by name 'exec (@ SQL) -- SQL Server 2005 static SQL. Select M. *, N. average score, N. total from (select * from TB) a round (max (score) for course in (Chinese, Mathematics, Physics) B) m, (Select name, cast (AVG (score * 1.0) as decimal () average score, sum (score) total score from TB group by name) nwhere M. name = n. name -- SQL Server 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 round (max (score) for course in ('+ @ SQL +') B) m, (Select name, cast (AVG (score * 1.0) as decimal () average score, sum (score) total score from TB group by name) nwhere M. name = n. name ') Drop table TB
If the two tables change to one another, the table structure and data are as follows: name: Chinese, mathematics, physics, Zhang San 74 83 93 Li Si 74 84 94 (expected result ): name course score ---- 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 Tb (name varchar (10), Chinese int, mathematical int, physical INT) insert into TB values ('zhang san', 2000, 93) insert into TB values ('Li si', 94) Go -- SQL Server static SQ L. Select * from (Select name, course = 'China', score = Chinese from TB Union all select name, course = 'mat', score = mathematics from TB Union all select name, course = 'physical ', score = physical from TB) torder by name, case course when 'chine' then 1 when' math 'then 2 when' then 3 end -- SQL Server 2000 dynamic SQL. -- Call the dynamic ecosystem of the system table. Declare @ SQL varchar (8000) Select @ SQL = isnull (@ SQL + 'Union all', '') + 'select name, [course] = '+ quotename (name, '''') + ', [score] =' + quotename (name) + 'from tb' from syscolumns where name! = N'name' and ID = object_id ('tb') -- table name TB, order by colid ascexec (@ SQL + 'order by name') -- SQL Server 2005 dynamic SQL. Select name, course, score from TB Untitled (score for course in ([language], [mathematics], [physics]) t -- SQL Server 2005 dynamic SQL, same as SQL Server 2000 dynamic SQL. --------------------/* Problem: add an average score and the total score to the above result. The following result is obtained: name course score ---- ------ 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, course = 'China', score = Chinese from TB Union all select name as name, course = 'mat', score = mathematics from TB Union all select name as name, course = 'physical ', score = physical from TB Union all select name as name, course = 'evenly balanced', score = cast (Language + mathematics + physics) * 1.0/3 as decimal () from TB Union all select name as name, course = 'Total', score = Chinese + mathematics + Physical from TB) torder by name, case course when 'chine' then 1 when' math 'then 2 when' then 3 when' average score 'then 4 when' total score 'then 5 enddrop table TB --> Generate Test Data: # db_infoif object_id ('tempdb. DBO. # db_info ') is not null drop table # db_infocreate table # db_info (SID int, name nvarchar (4), sex nvarchar (2) insert into # db_infoselect 1, 'lilim ', 'male' Union allselect 2, 'jun Wang ', 'male' Union allselect 3, 'limin', 'femal' --> Generate Test Data: # db_scoresif object_id ('tempdb. DBO. # db_scores ') is not null drop table # db_scorescreate table # db_scores (SID int, type nvarchar (4), scores INT) insert into # db_scoresselect 1, 'China ', 80 Union allselect 1, 'mat', 90 Union allselect 2, 'China', 85 Union allselect 2, 'mat', 90 Union allselect 3, 'China ', 75 Union allselect 3, 'mat', 85 declare @ SQL nvarchar (4000) set @ SQL = 'select. sid,. name,. sex 'select @ SQL = @ SQL + ', max (case when B. type = ''' + Type + ''' then B. scores else 0 end) ['+ Type +'] 'from (select distinct type from # db_scores) texec (@ SQL + 'from # db_info a left Outer Join # db_scores B on. SID = B. sid group by. sid,. name,. sex ')/* SID Name sex mathematical language ----------- ---- ----------- 1 Li Ming male 90 802 Wang Jun male 90 853 Li Min female 85 75 (3 rows affected )*/
