mssql行转列

create table PayPhoneMoney
  (
      id int identity(1,1),
      userName Nvarchar(20),
      payType nvarchar(20),
      money   decimal,
      payTime datetime,
      constraint pk_id primary key(id)
 )
 --插入测试数据
 insert into PayPhoneMoney values('小李','支付宝',20,'2012-01-03')
 insert into PayPhoneMoney values('小陈','工行',20,'2012-01-06')
 insert into PayPhoneMoney values('小赵','交行',50,'2012-01-06')
 insert into PayPhoneMoney values('小陈','支付宝',60,'2012-01-06')
 insert into PayPhoneMoney values('小赵','工行',30,'2012-01-16')
 insert into PayPhoneMoney values('小张','中行',30,'2012-01-16')
 insert into PayPhoneMoney values('小李','支付宝',60,'2012-01-16')


--分组查询
select paytype from payphonemoney group by paytype
select username,
     sum(case paytype when '支付宝' then money else 0 end) as 支付宝,
     sum(case paytype when '中行' then money else 0 end) as 中行,
     sum(case paytype when '交行' then money else 0 end) as 交行,
     sum(case paytype when '工行' then money else 0 end) as 工行,
     sum(money) as 合计
from payphonemoney
group by username


--拼接字符串
declare @sql varchar(500)
set @sql='select username,'
select @sql=@sql+' sum(case paytype when '''+paytype + ''' then money else 0 end) as '+ paytype +',' from payphonemoney group by paytype
set @sql=@sql + ' sum(money) as 合计'
set @sql=@sql + ' from payphonemoney group by username'
exec(@sql)