行列转换

Create table test (name char(10),km char(10),cj int)
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',77)

--查询
declare @sql varchar(8000),@s1 varchar(8000)
select @sql = '',@s1=''

select @sql = @sql+ ',['+km+']=sum(case km when '''+km+''' then cj else 0 end)'
,@s1=@s1+',sum(case km when '''+km+''' then cj else 0 end)/sum(case km when '''+km+''' then 1 else 0 end)'
from test
group by km
exec('select name=case grouping(name) when 1 then ''全班总分'' else name end'+@sql+',小计=sum(cj)
from test
group by name with rollup
union all
select ''全班平均分'''+@s1+',sum(cj)/count(distinct name)
from test')
go

--删除测试
drop table test

/*--测试结果

name 数学 英语 语文 小计
---------- ----------- ----------- ----------- -----------
李四 85 77 78 240
张三 86 75 80 241
全班总分 171 152 158 481
全班平均分 85 76 79 240
--*/


--------------------------------------------------------------------------
create table A
(
CSC_ID varchar(10),
NAME varchar(20),
MYD varchar(20),
Answer varchar(20)
)
insert A
select 'GS011','范学喜','维修时间满意度','满意' union
select 'GS011','范学喜','服务态度满意度','满意' union
select 'GS011','范学喜','总体满意度','非常满意' union
select 'GS033','邢金海','维修时间满意度','满意' union
select 'GS033','邢金海','服务态度满意度','满意' union
select 'GS033','邢金海','总体满意度','非常满意' union
select 'GS033','马德芳','维修时间满意度','满意' union
select 'GS033','马德芳','服务态度满意度','满意' union
select 'GS033','马德芳','总体满意度','满意' union
select 'GS090','陈巧林','维修时间满意度','满意' union
select 'GS090','陈巧林','服务态度满意度','不满意' union
select 'GS090','陈巧林','总体满意度','一般'

--查询
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+',max(case when MYD='''+MYD+''' then Answer end) as '''+MYD+''''
from A group by MYD
select @sql='select CSC_ID,NAME'+@sql+' from A group by CSC_ID,NAME'
exec(@sql)



--------------------------------------------
这个是邹建提供的例子：希望能解决你的问题 你可照着改一下
--成绩统计示例
--测试表
create table #t(xh varchar(3),xm varchar(10),km varchar(10),cj int)
insert into #t
select '001','张三','语文',80
union all select '001','张三','数学',85
union all select '002','李四','语文',90
union all select '002','李四','数学',80
union all select '003','王五','语文',70
union all select '003','王五','数学',78

--数据处理
declare @sql nvarchar(4000),@sql1 nvarchar(4000)
select @sql='',@sql1=''
select @sql=@sql+',['+km
+']=sum(case km when '''+km+''' then cj else 0 end)'
,@sql1=@sql1+',['+km+'名次]=(select sum(1) from # where ['
+km+']>=a.['+km+'])'
from(select distinct km from #t) a
exec('select xh 学号,xm 姓名'+@sql+',总成绩=sum(cj)
,总名次=(select sum(1) from(select xh,aa=sum(cj) from #t group by xh) aa where sum(a.cj)<=aa)
into # from #t a group by xh,xm
select *'+@sql1+' from # a
')


drop table #t

/*--测试结果

学号 姓名 数学 语文 总成绩 总名次 数学名次 语文名次
---- ------ ------- -------- ----------- ----------- ----------- -----------
002 李四 80 90 170 1 2 1
003 王五 78 70 148 3 3 3
001 张三 85 80 165 2 1 2

--*/



-------------------------------------------------------------------------
可以汇总两列数据
declare @sql varchar(8000),@s1 varchar(8000)
select @sql = '',@s1=''

select @sql = @sql+ ',['+simplename+']=sum(case simplename when '''+simplename+''' then num else 0 end)'
+ ',[【'+simplename+'】得分]=sum(case simplename when '''+simplename+''' then score else 0 end)'
from TempStatisfy
group by simplename
print @sql

exec('select Domain_Name as ''区域'',Province_Name as ''省份'' ,CSC_ID as ''客户认可号'' ,CSC_Type as ''单位性质'' ,CSC_Name as ''单位名称'' '+@sql+'
from TempStatisfy
group by Domain_Name,Province_Name,CSC_ID,CSC_Type,CSC_Name
')





-------------------------------------------

Create table TTTTTTT (csc_id char(10),product_name char(10),check_isoob bit)

insert TTTTTTT values('js001','700-U ',1)
insert TTTTTTT values('js001','700-UT ',1)
insert TTTTTTT values('js001','702-KM6 ',0)
insert TTTTTTT values('js001','702-A66+ ',0)

insert TTTTTTT values('js002','700-U ',1)
insert TTTTTTT values('js002','700-UT ',1)
insert TTTTTTT values('js002','702-KM6 ',0)
insert TTTTTTT values('js002','702-A66+ ',0)



declare @sql varchar(8000),@s1 varchar(8000)
select @sql = '',@s1=''

select @sql = @sql+ ',['+product_name+']=count(case when product_name ='''+product_name+''' and check_isoob=1 then check_isoob end)'
,@s1=@s1+',count(case when product_name= '''+product_name+''' and check_isoob=1 then check_isoob end)/count(case when product_name ='''+product_name+''' and check_isoob=1 then end)'
from ttttttt
group by product_name
print @sql
exec('select csc_id=case grouping(csc_id) when 1 then ''合计'' else csc_id end'+@sql+',合格=count(case when check_isoob=1 then check_isoob end ),不合格=count(case when check_isoob=0 then check_isoob end ),合计=count( check_isoob )
from ttttttt
group by csc_id with rollup
')