select name ,type1=sum(case moneyType when 'type1' then [money] else 0 end) ,type2=sum(case moneyType when 'type2' then [money] else 0 end) ,type3=sum(case moneyType when 'type3' then [money] else 0 end) from 表 group by name
--没注意,看楼主的结构,应该是:select name ,type1=case moneyType when 'type1' then [money] else 0 end ,type2=case moneyType when 'type2' then [money] else 0 end ,type3=case moneyType when 'type3' then [money] else 0 end from 表
--生成新表就用: select name ,type1=case moneyType when 'type1' then [money] else 0 end ,type2=case moneyType when 'type2' then [money] else 0 end ,type3=case moneyType when 'type3' then [money] else 0 end into 新表 from 表
--如果moneyType的值不只type1,type2,type3这几个.就要用动态的方法: declare @s varchar(8000) set @s='' select @s=@s+',['+moneyType+']' from(select distinct moneyType from 表) a exec('select name'+@s+' into 新表 from 表')
,type1=sum(case moneyType when 'type1' then [money] else 0 end)
,type2=sum(case moneyType when 'type2' then [money] else 0 end)
,type3=sum(case moneyType when 'type3' then [money] else 0 end)
from 表 group by name
,type1=case moneyType when 'type1' then [money] else 0 end
,type2=case moneyType when 'type2' then [money] else 0 end
,type3=case moneyType when 'type3' then [money] else 0 end
from 表
select name
,type1=case moneyType when 'type1' then [money] else 0 end
,type2=case moneyType when 'type2' then [money] else 0 end
,type3=case moneyType when 'type3' then [money] else 0 end
into 新表
from 表
declare @s varchar(8000)
set @s=''
select @s=@s+',['+moneyType+']' from(select distinct moneyType from 表) a
exec('select name'+@s+'
into 新表
from 表')