sql查询语句行转列的高效写法

jacky4955

　　/*
　　Create Table cj
　　(
　　姓名 varchar(10),
　　学科 varchar(10),
　　分数 Int
　　)
　　INSERT INTO cj (姓名,学科,分数) values (N'张三',N'语文',80)
　　INSERT INTO cj (姓名,学科,分数) values (N'张三',N'数学',90)
　　INSERT INTO cj (姓名,学科,分数) values (N'张三',N'外语',85)
　　INSERT INTO cj (姓名,学科,分数) values (N'李四',N'语文',81)
　　INSERT INTO cj (姓名,学科,分数) values (N'李四',N'数学',85)
　　INSERT INTO cj (姓名,学科,分数) values (N'李四',N'外语',90)
　　INSERT INTO cj (姓名,学科,分数) values (N'李四',N'体育',90)
　　INSERT INTO cj (姓名,学科,分数) values (N'王五',N'体育',98)
　　INSERT INTO cj (姓名,学科,分数) values (N'赵六',N'物理',90)
　　INSERT INTO cj (姓名,学科,分数) values (N'赵六',N'生物',98)
　　INSERT INTO cj (姓名,学科,分数) values (N'Bruce',N'生物',95)
　　INSERT INTO cj (姓名,学科,分数) values (N'Bruce',N'体育',97)
　　INSERT INTO cj (姓名,学科,分数) values (N'Bruce',N'语文',99)
　　INSERT INTO cj (姓名,学科,分数) values (N'Bruce',N'数学',100)
　　INSERT INTO cj (姓名,学科,分数) values (N'Bruce',N'外语',95)
　　INSERT INTO cj (姓名,学科,分数) values (N'Bruce',N'物理',99)
　　*/
　　------静态写法（适合学科比较少的）
　　Select *,语文+数学+外语 As '总分' Into #Tmp From(
　　Select 姓名,
　　Sum(CAse 学科 When '语文' Then 分数 Else 0 End) As '语文',
　　Sum(CAse 学科 When '数学' Then 分数 Else 0 End) As '数学',
　　Sum(CAse 学科 When '外语' Then 分数 Else 0 End) As '外语'
　　From cj Group By 姓名) T
　　Select * From #Tmp Union
　　Select '总分',Sum(语文),Sum(数学),Sum(外语),Sum(总分) From #Tmp
　　Drop Table #Tmp
　　-----动态写法(不管有多少学科都可以，如大学里每个人选修的课程不一样，且课程数量比较多)
　　Declare @sql varchar(4000)--行转列的SQL
　　Declare @RightSum varchar(2000)--右边求和的SQL
　　Declare @BottomSum varchar(2000)--底部求和的SQL
　　---1、SQL：先把行转成列，并插入一个新表临时表#T1
　　Set @sql = ' Select * Into #T1 From (Select 姓名,'
　　Select @sql = @sql + 'Sum(Case 学科 When '''+学科+''' Then 分数 Else 0 End) as '''+学科+''','
　　From (Select Distinct 学科 From cj) As a
　　Select @sql = Left(@sql,len(@sql)-1) +' From cj group by 姓名) Y '
　　---2、SQL：从T1求右边和并插入临时表#T2中
　　Set @RightSum=' Select *,'
　　Select @RightSum=@RightSum+''+学科+'+'
　　From (Select Distinct 学科 From cj) As b
　　Select @RightSum = Left(@RightSum,len(@RightSum)-1) + ' As ''总分'' Into #T2 From #T1 '
　　---3、SQL：列出所有及求底部和
　　Set @BottomSum=' Select * From #T2 Union Select ''总分'','
　　Select @BottomSum=@BottomSum+'Sum('+学科+'),'
　　From (Select Distinct 学科 From cj) As b
　　Select @BottomSum = Left(@BottomSum,len(@BottomSum)-2) + '),sum(总分) From #T2'
　　---4、执行
　　Exec(@sql+@RightSum+@BottomSum)---连接顺序：1、@sql 2、@RightSum 3、@BottomSum
　　---输出查看语句
　　Pr
　　int @sql
　　Print @RightSum
　　Print @BottomSum
　　/*
　　如果要单步执行，则不能用临时表，把#T1、#T2的#去掉即可。这是因为单步执行完成后临时表也就被删除了：
　　--1、
　　Exec (@sql)
　　--2、
　　Exec(@RightSum)
　　--3、
　　Exec(@BottomSum)
　　--4、执行完后删除表
　　Drop Table T1
　　Drop Table T2
　　建议不要单步执行，因为会产生实体表(T1、T2),如果多人同时访问时就会出问题；而用临时表(#T1、#T2)多人同时访问时互不影响。
　　*/