SQL Server row and column conversions

• SQL Server row to column

--Create a row to list and insert dataCreate TableTb_rowconverttocolumn (usernamenvarchar( -)NULL, Coursenvarchar( -)NULL, score numeric (Ten,2)NULL)Insert  intoTb_rowconverttocolumn (Username,course,score)Values('Zhang San','language', the)Insert  intoTb_rowconverttocolumn (Username,course,score)Values('Zhang San','Mathematics', -)Insert  intoTb_rowconverttocolumn (Username,course,score)Values('Zhang San','Foreign Languages', -)Insert  intoTb_rowconverttocolumn (Username,course,score)Values('John Doe','language', the)Insert  intoTb_rowconverttocolumn (Username,course,score)Values('John Doe','Mathematics', the)Insert  intoTb_rowconverttocolumn (Username,course,score)Values('John Doe','Foreign Languages', the)Insert  intoTb_rowconverttocolumn (Username,course,score)Values('Harry','language', the)Insert  intoTb_rowconverttocolumn (Username,course,score)Values('Harry','Mathematics',94)Insert  intoTb_rowconverttocolumn (Username,course,score)Values('Harry','Foreign Languages', the)--1. Static SQL row To column, this SQL specifies the column header of the transformationSelectusername name,MAX( CaseCourse when 'language'  ThenScoreElse 0 End) languages,MAX( CaseCourse when 'Mathematics'  ThenScoreElse 0 End) Mathematics,MAX( CaseCourse when 'Foreign Languages'  ThenScoreElse 0 End) Foreign Language fromTb_rowconverttocolumnGroup  byusernameOrder  byusername/*Name Language Mathematics foreign Language John Doe 86.00 82.00 92.00 Harry 82.00 94.00 82.00 Zhang three 82.00 85.00 90.00*/--2. Static SQL row-to-column, which specifies the column header of the transformation, which must be sqlserver2005 and above to be usedSelectUsername name, Chinese, maths, foreign language fromTb_rowconverttocolumn Pivot (Max(score) forCourseinch(Chinese, maths, foreign language)) a/*Name Language Mathematics foreign Language John Doe 86.00 82.00 92.00 Harry 82.00 94.00 82.00 Zhang three 82.00 85.00 90.00*/Select *  fromTb_rowconverttocolumn Pivot (Max(score) forCourseinch(Chinese, maths, foreign language)) a/*username Chinese Mathematics foreign language John Doe 86.00 82.00 92.00 Harry 82.00 94.00 82.00 Zhang three 82.00 85.00 90.00*/--3. Dynamic SQL row-to-column, automatically generate converted columnsDeclare @sql nvarchar( -)Select distinctCourse into#tb_group fromTb_rowconverttocolumnOrder  byCoursedesc--table headers and sortingSelect @sql=ISNULL(@sql+',',"')+'MAX (case course when" "+Course+" "Then score Else 0 end) ['+Course+']'  from#tb_groupSet @sql='Select username name,'+@sql        +'From Tb_rowconverttocolumn a'        +'GROUP BY username'exec(@sql)Drop Table#tb_group/*name Chinese Foreign language mathematics John Doe 86.00 92.00 82.00 Harry 82.00 82.00 94.00 Zhang three 82.00 90.00 85.00*/--4. Dynamic SQL row to column, automatically generate the converted column, the statement must be sqlserver2005 and above to useDeclare @sql nvarchar( -)Select @sql=ISNULL(@sql+',',"')+Course fromTb_rowconverttocolumnGroup  byCourseSet @sql='SELECT * from Tb_rowconverttocolumn pivot (max (score) for course in ('+@sql+')) a'exec(@sql)/*Username Mathematics Foreign Language John Doe 82.00 92.00 86.00 Harry 94.00 82.00 82.00 Zhang three 85.00 90.00 82.00*/

• SQL Server column career

--Create a column change table and insert dataCreate TableTb_columnconverttorow ([name] nvarchar( -)NULL,   [language] nvarchar( -)NULL,   [Mathematics] nvarchar( -)NULL,   [Foreign Languages] nvarchar( -)NULL)Insert  intoTb_columnconverttorow (name, language, mathematics, foreign language)Values('John Doe', the, the, the)Insert  intoTb_columnconverttorow (name, language, mathematics, foreign language)Values('Harry',94, the, the)Insert  intoTb_columnconverttorow (name, language, mathematics, foreign language)Values('Zhang San', -, -, the)--1. Static SQL column line change, when the column header is less usedSelect *  from (   SelectName, course='language'Scores=Chinese fromTb_columnconverttorowUnion  All   SelectName, course='Mathematics'Scores=Mathematical fromTb_columnconverttorowUnion  All    SelectName, course='Foreign Languages'Scores=Foreign language fromTb_columnconverttorow) A/*Name Course score John Doe language 82 Harry Language 94 three languages 85 John Doe Mathematics 92 Harry Mathematics 82 Zhang Three mathematics 90 Li four foreign languages 86 Harry Foreign Languages 82 three foreign languages*/--2. Static SQL column career, when the column header is used, the statement must be sqlserver2005 and above to useSelectName, course, score fromTb_columnconverttorow Unpivot (Score forCourseinch(Chinese, maths, foreign language)) a/*Name Course score John Doe language 82 John Doe Mathematics 92 Li Four foreign languages 86 Harry Languages 94 Harry Mathematics 82 Harry foreign Languages 82 three languages 85 Zhang Three Mathematics 90 three foreign languages*/

SQL Server row and column conversions