SQL row conversions to columns and column conversions to rows

This is the script file that created the database

CREATE TABLE [dbo]. [Stu] ([study number] [nvarchar] (255) not null,[name] [nvarchar] (255) null,[sex] [nvarchar] (255) null,[Professional] [nvarchar] (255) null,[faculties] [ NVARCHAR] (255) NULL) on [Primary]goinsert [dbo]. [Stu] ([study number], [name], [gender], [professional], [faculty]) VALUES (n ' 111405060432 ', n ' Wang Xiaoming ', n ' Men ', n ' financial department ', N ' Economics College ') INSERT [dbo]. [Stu] ([study number], [name], [gender], [professional], [faculty]) VALUES (n ' 07102060215 ', n ' Wang Shuo Q ', n ' Men ', n ' material forming and control engineering ', n ' College of Materials Science and Engineering ') INSERT [dbo]. [Stu] ([study number], [name], [gender], [professional], [faculty]) VALUES (n ' 07104060407 ', n ' Feng Jing ', n ' Women ', n ' Finance ', N ' School of Economics ') INSERT [dbo]. [Stu] ([study number], [name], [gender], [professional], [faculty]) VALUES (n ' 07108040122 ', n ' Wang Niuzhe ', n ' Men ', N ' Environmental Engineering ', n ' chemical and Pharmaceutical Academy ') INSERT [dbo]. [Stu] ([study number], [name], [gender], [professional], [faculty]) VALUES (n ' 07110020114 ', n ' Liu Long ', N ' Men ', n ' Applied Physics ', N ' Physics and Engineering school ')

　　

--------row value to column value use Case statement method A select study number, Max (case professional when'Department of Finance'Then facultyElseFaculties END) as'Department of Finance', Max (case professional when'material forming and control engineering'Then facultyElseFaculties END) as'material forming and control engineering', Max (case professional when'Financial Science'Then facultyElseFaculties END) as'Financial Science', Max (case professional when'Environmental Engineering'Then facultyElseFaculties END) as'Environmental Engineering', Max (case professional when'Applied Physics'Then facultyElseFaculties END) as'Applied Physics'From Stugroup by study number go------row to convert column values------method twodeclare @sql varchar (8000)Select@sql =isnull (@sql +',',"')+'Max (case professional when" "+ Professional +" "Then faculty Else faculty end) ['+ Professional +']' from(SelectDistinct professional fromStu asaSet@sql ='Select study number,'[Email protected]+'From Stu GROUP by study number'exec (@sql) go-----use ISNULL ()-----DECLARE @sql varchar (8000)Select@sql =isnull (@sql +',',"') + Professional fromStu GROUP BY professionalSet@sql ='select * from Stu Pivot (MAX (faculty) for professional in ('[Email protected]+')) a'exec (@sql) go
----------Use pivot--------

Select*-----The column to be selected, *Delegates Select all fromStu-------which result sets the data pivot (MAX (faculty)--The aggregate function Max indicates how you need to handle the value of the converted column, whether it's sum (sum), average (avg), Min,max, and so on.  forProfessional---- forProfessional means that the value of the Professional column is converted to a column, that is, "column by value." inch-------we just want to take some of these values into columns, so how do we get them? is to write the values in the in inside (School of Materials Science and engineering, School of Economics, Institute of Chemical and Pharmaceutical Sciences, School of Physics and Engineering, finance))--- asb Go ----- ------Career Change--------------------------Use the SQL Server 2005 dynamic sqldeclare @sql nvarchar (4000)Select@sql =isnull (@sql +',',"')+QuoteName (Name) fromsyscolumnswhereID=OBJECT_ID ('Stu') and Name notinch('name','School Number','Professional','Sex') Order by ColidSet@sql ='Select study number, name, [professional], gender from Stu Unpivot ([AA] for [BB] in ('[Email protected]+')) b'exec (@sql) go
------Method Two------------Select* from( SelectSchool number, name, gender, professional ='Department of Finance', faculties ='School of Economics'  fromStu Union AllSelectSchool number, name, gender, professional ='material forming and control engineering', faculties ='School of Materials science and engineering'  fromStu Union AllSelectSchool number, name, gender, professional ='Financial Science', faculties ='School of Economics'  fromStu) T order by school number go
----------Use Unpivot----------
SelectAa,bb fromStu Unpivot (AA forBbinch([Department of],[Professional])) T