假设有张学生成绩表(tb)如下:
Name Subject Result
张三 语文 及格
张三 数学 及格
张三 物理 良好
李四 语文 及格
李四 数学 良好
李四 物理 差
改成:
想变成
姓名 语文 数学 物理
---------- ----------- ----------- -----------
李四 及格 良好 差
张三 及格 及格 良好
注意科目不是分数,请高手帮忙!!!!
create table tb
(
Name varchar(10) ,
Subject varchar(10) ,
Result varchar(10)
)
insert into tb(Name , Subject , Result) values('张三' , '语文' , '及格')
insert into tb(Name , Subject , Result) values('张三' , '数学' , '及格')
insert into tb(Name , Subject , Result) values('张三' , '物理' , '良好')
insert into tb(Name , Subject , Result) values('李四' , '语文' , '及格')
insert into tb(Name , Subject , Result) values('李四' , '数学' , '良好')
insert into tb(Name , Subject , Result) values('李四' , '物理' , '差')
select * from tb
DECLARE @sql nvarchar(4000)
DECLARE @subject nvarchar(10)
SET @sql='select name'
DECLARE subject_cursor CURSOR FOR
SELECT DISTINCT subject
FROM tb
OPEN subject_cursor
FETCH NEXT FROM subject_cursor
INTO @subject
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql=@sql + ',max(case subject when ''' + @subject + ''' then result else '''' end ) as ' + @subject
FETCH NEXT FROM subject_cursor
INTO @subject
END
CLOSE subject_cursor
DEALLOCATE suject_cursor
set @sql = @sql + ' from tb group by name'
print @sql
EXEC sp_executesql @sql
declare @sql varchar(4000)
set @sql = 'select EXPENSE_KIND AS 消费方式,SUM(SUM_MONEY) AS 合计'
select @sql = @sql + ',isnull(sum(case JS_WAY when '''+JS+''' then SUM_MONEY end),0) ['+JS+']'
from (select JS=
case
WHEN JS_WAY IS NULL THEN '未知'
WHEN JS_WAY = '' THEN '未知'
ELSE JS_WAY
END
from OPERATION_EXPENSE_BILL GROUP BY JS_WAY) as a
select @sql = @sql+' from OPERATION_EXPENSE_BILL '