sql行列转换方法整理(国外英文资料).docVIP

sql行列转换方法整理 -- line shift Original: name subject score Chinese 80 Three mathematics 90 Zhang SAN physics 85 Li 4 Chinese 85 Li 4 physics 82 Li 4 English 90 Li iv politics 70 Wang 5 English 90 Transformed table: name mathematical physical English language politics Li: 4, 0, 82, 90, 85, 70 Wang 5, 0, 0, 0, 0, 0 Its 3, 3, 90, 85, 0, 80, 0 Example: Create table cj -- create table cj ( ID Int IDENTITY (1, 1) not null - creates the column ID and adds 1 each time the new record is added Name Varchar (50), The Subject Varchar (50), The Result Int, Primary key (ID) - defines the primary key of the table cj ); - the Truncate table cj Select * from cj Insert into cj Select three, Chinese, 80 union all Select three, math, 90 union all Select three, physics, 85 union all Select li 4, Chinese, 85 union all Select li 4, physics, 82 union all Select li 4, English, 90 union all Select li 4, politics, 70 union all Select wang 5, English, 90 Line conversion Declare @ SQL varchar (8000). Set @sql = Select Name as the Name The Select @sql = @sql + , sum is then Result else 0 end [ + Subject +] From the select distinct Subject from cj, the name of all the only subjects is listed Select @sql = @sql + from cj group by name The Exec (@ SQL) Line conversion -- merge Original: class number One, one One, two One, three Two, one Two, two Three, one Converted: class number 1, 1, 2, 3 2 1, 2, 3 1 Example: Create table ClassNo -- Create a table ClassNo ( ID Int IDENTITY (1, 1) not null - creates the column ID and adds 1 each time the new record is added Class Varchar (50) - Class column Number Varchar (50), - the student Number column Primary Key (ID) - the Key that defines the ID as the table ClassNo ); - the Truncate Table ClassNo -- Select * from ClassNo Insert Into ClassNo Select 1, 1 Union all Union all Select 1, 2 Select 1, 3 Union all Select 2, 1 Union all Union all Select 2, 2 Select 3, 1 Create a merged function - Drop Function KFReturn Create Function KFReturn (@class Varchar (50)) Retu