PIVOT and Unpivot

Original: http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html

MSDN Documentation: Https://msdn.microsoft.com/zh-cn/library/ms177410.aspx

According to the original text, a slight change, code specifications.

--First, row column--1, establish the form if object_id (' TB ') is not null drop table TB go CREATE TABLE TB (name varchar (10), course varchar (10), score int) SET NOCOUNT on INSERT into TB values (' John ', ' language ', ') insert into TB values (' John ', ' Math ', ') insert into TB values (' John ', ' Physics ', 93 INSERT into TB values (' Dick ', ' language ', ') ' INSERT into TB values (' Dick ', ' math ', ') insert into TB values (' Dick ', ' physics ', ') go SELECT *  From TB/* Name Course score John Language 74 three mathematics 83 three physics 93 Li four languages 74 Dick Mathematics 84 Dick Physics 94/--2, using SQL Server 2000 static SQL (must know a few courses) select Name, Max (case course when ' language ' then score else 0 end) language, Max (case course when ' math ' then score else 0 end) mathematics, Max (case course when ' physics ' then
Score Else 0 end) physical from TB Group by name/name Language Math Physics Lee 474 84 94 374 83/--3, using SQL Server 93 dynamic SQL (no need to know how many courses) --Method 1 declare @sql1 varchar set @sql1 = ' Select name ' Select @sql1 = @sql1 + ', max (case course when ' + course + ' "' then score else 0 end [' + course + '] ' from (select DISTINCT course from TB) A--with the From TB Group by course, by default by course name Sort Set @sql1 = @sql1 + ' from TB Group by name '--prin T @sql1 EXEC (@sql1)/* Last NameMathematics and Physics language Lee 484 94 74 383 93 74 * *-Method 2 (null+string=null) declare @sql2 varchar (8000) Select @sql2 =isnull (@sql2 + ' , ', ', + ' max (case course when ' + course + ' ' then score else 0 end) [' + Course + '] ' from (select DISTINCT course from TB) as a set @sql2 = ' Select name, ' + @sql2 + ' from TB Group by name ' EXEC (@sql2)//name Mathematical Physics language Lee 484 94 74 383 93 74/--4, using SQL Server 200 5 Static SQL SELECT * from TB pivot (max (fractions) for course in (Chinese, maths, physics)) A/* Name language Math Physics Lee 474 84 94 374 83 93/--5, using SQL Se RVer 2005 Dynamic SQL--using stuff () declare @sql51 varchar (8000) Set @sql51 = '--Initialize variable @sql51 Select @sql51 = @sql51 + ', ' + course from TB GROUP BY course--Variable multivalued assignment set @sql51 = Stuff (@sql51, 1, 1, ')--Remove the first ', ' Set @sql51 = ' SELECT * from TB pivot (max score) for course in (' +@s ql51+ ') A ' EXEC (@sql51)/* Name Math Physics language Lee 484 94 74 383 93 74 * * or use ISNULL () declare @sql52 varchar (8000)--Get the course set  Combined Select @sql52 =isnull (@sql52 + ', ', ') + course from TB GROUP BY course Set @sql52 = ' SELECT * from TB pivot (MAX (score) for course In (' + @sql52 + ')) a ' EXEC (@sql52)/* Name Math Physics language Lee 484 94 74 383 93 74 * * * Two, row-and-column results plus total score, average--1, using SQL Server 2000 static SQL--sql Server 2000 static State SQL select name, max (case course when ' language ' then score else 0 end) language, Max (case course when ' math ' then score else 0 end) mathematics, Max (case course when ' Physical ' then score else 0 end ' physics, sum (score) total score, CAST (avg (fractional *1.0) as decimal (18,2)) as average from TB group by name//Name Language Mathematics Physics total score Average Cent Li 474 84 94 252 84.00 374 83 93 + 250 83.33/--2, using SQL Server 2000 dynamic SQL--sql Server 2000 dynamic SQL declare @sql VA Rchar Set @sql = ' Select name ' Select @sql = @sql + ', max (case course when ' + course + ' ' then score else 0 end) [' + Course + '] ' from (select Di STINCT course from TB) a set @sql = @sql + ', sum (score) as Total, cast (avg (fractional *1.0) as decimal (18,2)) as average from TB group by name ' EXEC (@sq L)/* Name The total score of the mathematical Physics language is 484 94 74 252 84.00 383 93, 74 250 83.33/--3, using SQL Server 2005 static SQL Select M.*,n. Total Score , N. Average separation from (SELECT * from TB pivot (MAX (score) for course in (Chinese, maths, physics)) as M, (select name, sum (score) as Total, cast (avg.) (Score *1. 0) as decimal (18,2)) asAverage from TB group by name N where M. name =n. Name//name Chinese mathematical Physics total score is 474 84 94 252 84.00 Sheets 374 83 93 250 83.33 * * -4, use SQL Server 2005 Dynamic SQL--use stuff ()--Declare @sql_m2_41 varchar (8000) Set @sql_m2_41 = '--Initialize variable @sql_m2_41 select @sql _m2_41= @sql_m2_41 + ', ' + course from TB GROUP BY course-variable multivalued assignment-same as Select @sql_m2_41 = @sql_m2_41 + ', ' + course from (SELECT DISTINCT course  From TB) as a set @sql_m2_41 =stuff (@sql_m2_41, 1, 1, ')--Removes the first ', ' Set @sql_m2_41 = ' Select M.*, N. Total score, N. Average from (SELECT * from TB) a pivot (max (fractions) for course in (' + @sql_m2_41 + ')) m, (select name, sum (score) as total score, CAST (avg (fractional *1.0) as Deci Mal (18,2) as average from TB Group by name) n where m. Name = N. Name ' EXEC (@sql_m2_41)/* Name Mathematics Physics language Total score 484 94 74 252 84.0 0 383 93 74 250 83.33/-or use ISNULL () declare @sql_m2_42 varchar (8000) Select @sql_m2_42 =isnull (@sql_m2_42 + ', ', ') + Course from TB GROUP BY course Set @sql_m2_42 = ' Select M.*, N. Total score, N. Average point from (SELECT * from TB) a pivot (max score) for Course in (' + @sql_m2_42 +') b) m, (select name, sum (score) as Total, cast (avg (fractional *1.0) as decimal (18,2) as on average from TB group by name) n where m. Name = N. Name ' ex EC (@sql_m2_42)/* Name The total score of the Math Physics language is 484 94 74 252 84.00 383 93, 74 250 83.33 * *

--Three, column change--1, establish the form if object_id (' TB ') is not null drop table TB go CREATE TABLE TB (name varchar (10), language int, math int, physical int) I		Nsert into TB values (' John ', 74,83,93) inserts into TB values (' Dick ', 74,84,94) go SELECT * out TB Go * * Name Language Mathematics Physics Zhang 374 83
93 Li 474 84 94/--2, using SQL Server 2000 static SQL--sql Server 2000 static SQL. SELECT * FROM (select name, course = ' language ', score = language from TB union ALL select Name, course = ' math ', score = Math from TB UNION ALL select Name, course = ' Physics ' , score = physical from TB) T-order by name, case course when ' language ' then 1 when ' math ' then 2 when ' physics ' then 3 END * * Name Course score Dick language 74 Dick Math 8
4 Dick Physics 94 three languages 74 three mathematics 83 three physics 93/--2, using SQL Server 2000 dynamic SQL--sql Server 2000 dynamic SQL.
--Call the system table dynamic ecology. DECLARE @sql_cTor_21 varchar (8000) Select @sql_cTor_21 =isnull (@sql_cTor_21 + ' union All ', ') + ' select name, [Course]= ' +quotena Me (name, ' ') + ', [score] = ' +quotename (name) + ' from TB ' from syscolumns where name!= ' name ' and id=object_id (' TB ')--table name TB, do not include column name The other column order of the name by Colid exec (@sql_cTor_21 + ' ORDER by name ') Go/* Name Course score Dick Language 74 Dick Mathematics 84 Li four objectsDaniel 94 Three languages 74 three physics 93 math 83/--3, using SQL Server 2005 static SQL--sql Server 2005 Dynamic SQL select Name, course, score from TB Unpivot (score F or course in ([Chinese],[Mathematics],[Physics]) T/* Name Course score John Language 74 three mathematics 83 three physics 93 Li four languages 74 Dick Mathematics 84 Dick Physics 94/--4, using SQL Server 2005 Dynamic SQ L--sql SERVER 2005 Dynamic SQL declare @sql_cTor_4 nvarchar (4000) Select @sql_cTor_4 =isnull (@sql_cTor_4 + ', ', ') +quotename ( Name from syscolumns where id=object_id (' TB ') and Name not in (' names ') Order by Colid Set @sql_cTor_4 = ' select name, [Course],[score] F 	Rom TB unpivot ([score] for [course] in (' + @sql_cTor_4 + ')) B ' EXEC (@sql_cTor_4)/* Name Course score John Language 74 three mathematics 83 three physics 93 Li four languages 74 dick Mathematics 84 Dick Physics 94 */