SQL 行转列。解决方案
SQL 行转列。。。。急,急,急,急、、
现有表[Hong_Props],表中的字段如下:
PropID PropGameType PropArrea PropTime Props PropsCoun
1 1 1 2012-02-11 道具A 24
2 2 2 2012-02-11 道具B 15
3 1 1 2012-02-12 道具C 14
4 2 1 2012-02-12 道具D 2
5 2 2 2012-02-13 道具D 50
6 1 2 2012-02-14 道具B 9
7 2 2 2012-02-15 道具E 10
8 1 1 2012-02-15 道具A 20
先我要得到的查询效果为:
日期 2012-02-11 2012-02-12 2012-02-13 2012-02-14 2012-02-15 累计
道具A 24 / / / 20 44
道具B 15 / / 9 / 24
道具C / 14 / / / 14
道具D / 2 50 / / 52
道具E / / / / 10 10
急求高手帮忙!
急求高手帮忙!
急求高手帮忙!
急求高手帮忙!
------解决方案--------------------
现有表[Hong_Props],表中的字段如下:
PropID PropGameType PropArrea PropTime Props PropsCoun
1 1 1 2012-02-11 道具A 24
2 2 2 2012-02-11 道具B 15
3 1 1 2012-02-12 道具C 14
4 2 1 2012-02-12 道具D 2
5 2 2 2012-02-13 道具D 50
6 1 2 2012-02-14 道具B 9
7 2 2 2012-02-15 道具E 10
8 1 1 2012-02-15 道具A 20
先我要得到的查询效果为:
日期 2012-02-11 2012-02-12 2012-02-13 2012-02-14 2012-02-15 累计
道具A 24 / / / 20 44
道具B 15 / / 9 / 24
道具C / 14 / / / 14
道具D / 2 50 / / 52
道具E / / / / 10 10
急求高手帮忙!
急求高手帮忙!
急求高手帮忙!
急求高手帮忙!
------解决方案--------------------
- SQL code
declare @sql varchar(max) set @sql = 'select Props' select @sql = @sql + ',sum(case convert(varchar(8),PropTime,112) when '''+date+''' then PropsCoun else 0 end) ['+date+']' from( select convert(varchar(8),PropTime,112) date from Hong_Props group by convert(varchar(8),PropTime,112) ) t select @sql = @sql + ',sum(PropsCoun) as [累计] from Hong_Props group by Props ' exec(@sql)
------解决方案--------------------
- SQL code
create table Hong_Props( PropID int,PropGameType int,PropArrea int,PropTime datetime,Props varchar(20),PropsCoun int ) insert into Hong_Props select 1 ,1 ,1 ,'2012-02-11' ,'道具A' ,24 union all select 2 ,2 ,2 ,'2012-02-11' ,'道具B' ,15 union all select 3 ,1 ,1 ,'2012-02-12' ,'道具C' ,14 union all select 4 ,2 ,1 ,'2012-02-12' ,'道具D' ,2 union all select 5 ,2 ,2 ,'2012-02-13' ,'道具D' ,50 union all select 6 ,1 ,2 ,'2012-02-14' ,'道具B' ,9 union all select 7 ,2 ,2 ,'2012-02-15' ,'道具E' ,10 union all select 8 ,1 ,1 ,'2012-02-15' ,'道具A' ,20 go declare @sql varchar(max) set @sql = 'select Props' select @sql = @sql + ',sum(case convert(varchar(10),PropTime,120) when '''+date+''' then PropsCoun else 0 end) ['+date+']' from( select convert(varchar(10),PropTime,120) date from Hong_Props group by convert(varchar(10),PropTime,120) ) t order by date select @sql = @sql + ',sum(PropsCoun) as [累计] from Hong_Props group by Props ' exec(@sql) drop table Hong_Props /************************* Props 2012-02-11 2012-02-12 2012-02-13 2012-02-14 2012-02-15 累计 -------------------- ----------- ----------- ----------- ----------- ----------- ----------- 道具A 24 0 0 0 20 44 道具B 15 0 0 9 0 24 道具C 0 14 0 0 0 14 道具D 0 2 50 0 0 52 道具E 0 0 0 0 10 10 (5 行受影响)
------解决方案--------------------
- SQL code
--如果PropTime字段为字符串型 declare @sql varchar(8000) set @sql = 'select Props ' select @sql = @sql + ' , max(case PropTime when ''' + PropTime + ''' then PropsCoun else 0 end) [' + PropTime + ']' from (select distinct PropTime from Hong_Props) as a set @sql = @sql + ' ,sum(PropsCoun) 累计 from Hong_Props group by Props' exec(@sql) --如果PropTime字段为时间型 declare @sql varchar(8000) set @sql = 'select Props ' select @sql = @sql + ' , max(case PropTime when ''' + PropTime + ''' then PropsCoun else 0 end) [' + PropTime + ']' from (select distinct convert(varchar(10),PropTime,120) PropTime from Hong_Props) as a set @sql = @sql + ' ,sum(PropsCoun) 累计 from (select convert(varchar(10),PropTime,120) PropTime, Props ,PropsCoun from Hong_Props) t group by Props' exec(@sql)