我有一个表结构如下
userid Feedate feeType FeeCount
1 2006-7-1 生活费 300
2 2006-7-5 水费 50
3 2006-7-1 电费 43
1 2006-7-1 杂费 80现在要生成下面的格式
userid 2006-7-1 2006-7-5
1 380 0
2 0 50
3 43 0请问如何实行?
userid Feedate feeType FeeCount
1 2006-7-1 生活费 300
2 2006-7-5 水费 50
3 2006-7-1 电费 43
1 2006-7-1 杂费 80现在要生成下面的格式
userid 2006-7-1 2006-7-5
1 380 0
2 0 50
3 43 0请问如何实行?
(case when Feedate='2006-7-1' then sum(FeeCount) else 0 end ) as [2006-7-1],
(case when Feedate='2006-7-5' then sum(FeeCount) else 0 end ) as [2006-7-5]
from table1 group by userid,Feedate order by userid
(userid char(1),
feedate varchar(10),
feetype varchar(10),
feecount int)insert into test(userid,feedate,feetype,feecount)
select '1','2006-7-1','生活费',300 union all
select '2','2006-7-5','水费' ,50 union all
select '3','2006-7-1','电费' ,43 union all
select '1','2006-7-1','杂费' ,80 --select * from test--用于:交叉表的列数是不确定的
declare @sql varchar(8000)
set @sql = 'select userid,' select @sql = @sql + 'sum(case feedate when '''+feedate+'''
then feecount else 0 end) as '''+feedate+''','
from (select distinct feedate from test) as a
select @sql = left(@sql,len(@sql)-1) + ' from test group by userid'exec(@sql)
drop table test
select @sql=@sql+',sum(case when Feedate ='''+convert(varchar(10),Feedate,120)+''' then FeeCount else 0 end) as ['++convert(varchar(10),Feedate,120)+']'
from tablename
group by Feedate
order by Feedateexec ('select userid'+@sql+' from tablename group by userid')--没有测试
insert into tt
select 1,'2006-7-1','生活费',300 union
select 2,'2006-7-5','水费',50 union
select 3,'2006-7-1','电费',43 union
select 1,'2006-7-1','杂费',80declare @sql varchar(2000)
set @sql = 'select userid'
select @sql = @sql + ','+
quotename(Feedate) + ' = sum(case Feedate when ''' + Feedate + ''' then FeeCount else 0 end) '
from tt group by Feedate;
select @sql =@sql+' from tt group by userid'
exec(@sql)
select 1,'2006-7-1','生活费',300
union all select 2,'2006-7-5','水费',50
union all select 3,'2006-7-1','电费',43
union all select 1,'2006-7-1','杂费',80select * from #t
declare @sql nvarchar(1000)
set @sql='select userid'
select @sql=@sql+',sum(case convert(varchar(10),feedate,120)
when '''+convert(varchar(10),feedate,120)
+''' then feecount else 0 end) as ['+ convert(varchar(10),feedate,120) +']'
from (select distinct feedate from #t ) a
set @sql=@sql+' from #t group by userid order by userid'
print @sql
exec(@sql)
drop table #t