SQL 行转列

benben_tong 2012-02-20 05:06:36

现有表[Hong_Props],表中的字段如下：

PropID PropGameType PropArrea type 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

先我要得到的查询效果为：

日期道具A 道具B 道具C 道具D 道具E 累计
2012-02-11 24 15 0 0 0 39
2012-02-12 0 0 14 2 0 16
2012-02-13 0 0 0 50 0 50
2012-02-14 0 9 0 0 10 19
2012-02-15 20 0 0 0 10 30

急求高手帮忙！
急求高手帮忙！
急求高手帮忙！
急求高手帮忙！

...全文

138 11 打赏收藏举报

写回复

用AI写文章

11 条回复

切换为时间正序

请发表友善的回复…

发表回复

gaobinbinbin 2012-02-21

打赏
举报

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)as date from Hong_Props
group by convert(varchar(10),PropTime,120)
) t
select @sql=@sql+',sum(PropsCoun) as qq from Hong_Props group by props'
exec (@sql)

开着拖拉机泡妞 2012-02-20

打赏
举报

/*
现有表[Hong_Props],表中的字段如下：

PropID PropGameType PropArrea type 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

先我要得到的查询效果为：

日期道具A 道具B 道具C 道具D 道具E 累计
2012-02-11 24 15 0 0 0 39
2012-02-12 0 0 14 2 0 16
2012-02-13 0 0 0 50 0 50
2012-02-14 0 9 0 0 10 19
2012-02-15 20 0 0 0 10 30

*/

--生成测试数据：
go
if OBJECT_ID('Hong_Props')is not null
drop table Hong_Props
go
create table Hong_Props(
PropID int,
PropGameType int,
PropArrea int,
PropTime date,
Props varchar(20),
PropsCoun int
)
go
insert 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

declare @str varchar(1000)
set @str=''
select @str=@str+','+Props+
'=max(case when Props='+quotename(Props,'''')+' then PropsCoun else 0 end)'
from Hong_Props
group by Props
--print @str
select @str='select PropTime'+@str+',sum(PropsCoun) as 累计 from Hong_Props group by PropTime'
--print @str
exec (@str)

/*
PropTime 道具A 道具B 道具C 道具D 道具E 累计
2012-02-11 24 15 0 0 0 39
2012-02-12 0 0 14 2 0 16
2012-02-13 0 0 0 50 0 50
2012-02-14 0 9 0 0 0 9
2012-02-15 20 0 0 0 10 30
*/

--你这个查询也就是很标准的行列转换问题，动态实现的基本方法就是拼接查询语句

开着拖拉机泡妞 2012-02-20

打赏
举报

[Quote=引用 7 楼 szstephenzhou 的回复:]

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 a……
[/Quote]

这个帖子做过了，难道是楼主从发的？？其实你可以拿之前那个简单的修改一下就好了

中国风 2012-02-20

打赏
举报

参照方法,SQL2005以上版本可用pivot
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html

勿勿 2012-02-20

打赏
举报

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 @str varchar(max)

set @str = 'select convert(varchar(10),PropTime,120) PropTime'

select @str = @str + ',sum(case when Props='''+Props+''' then PropsCoun else 0 end) ['+Props+']'

from(select Props from Hong_Props group by Props ) t

select @str = @str +',SUM(PropsCoun) as 累计'+  ' from Hong_Props group by  PropTime'

print (@str)

exec(@str)





 



select convert(varchar(10),PropTime,120) PropTime,

sum(case when Props='道具A' then PropsCoun else 0 end) [道具A],

sum(case when Props='道具B' then PropsCoun else 0 end) [道具B],

sum(case when Props='道具C' then PropsCoun else 0 end) [道具C],

sum(case when Props='道具D' then PropsCoun else 0 end) [道具D],

sum(case when Props='道具E' then PropsCoun else 0 end) [道具E],

SUM(PropsCoun) as 累计 from Hong_Props group by  PropTime







PropTime   道具A         道具B         道具C         道具D         道具E         累计

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

2012-02-11 24          15          0           0           0           39

2012-02-12 0           0           14          2           0           16

2012-02-13 0           0           0           50          0           50

2012-02-14 0           9           0           0           0           9

2012-02-15 20          0           0           0           10          30



(5 行受影响)

勿勿 2012-02-20

打赏
举报

declare @str varchar(max)

set @str = 'select convert(varchar(10),PropTime,120) PropTime'

select @str = @str + ',sum(case when Props='''+Props+''' then PropsCoun else 0 end) ['+Props+']'

from(select Props from Hong_Props group by Props ) t

select @str = @str +',SUM(PropsCoun) as 累计'+  ' from Hong_Props group by  PropTime'

print (@str)

exec(@str)







PropTime   道具A         道具B         道具C         道具D         道具E         累计

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

2012-02-11 24          15          0           0           0           39

2012-02-12 0           0           14          2           0           16

2012-02-13 0           0           0           50          0           50

2012-02-14 0           9           0           0           0           9

2012-02-15 20          0           0           0           10          30



(5 行受影响)





---or



select convert(varchar(10),PropTime,120) PropTime,sum(case when Props='道具A' then PropsCoun else 0 end) [道具A],sum(case when Props='道具B' then PropsCoun else 0 end) [道具B],sum(case when Props='道具C' then PropsCoun else 0 end) [道具C],sum(case when Props='道具D' then PropsCoun else 0 end) [道具D],sum(case when Props='道具E' then PropsCoun else 0 end) [道具E],SUM(PropsCoun) as 累计 from Hong_Props group by  PropTime





PropTime   道具A         道具B         道具C         道具D         道具E         累计

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

2012-02-11 24          15          0           0           0           39

2012-02-12 0           0           14          2           0           16

2012-02-13 0           0           0           50          0           50

2012-02-14 0           9           0           0           0           9

2012-02-15 20          0           0           0           10          30



(5 行受影响)

叶子 2012-02-20

打赏
举报

[Quote=引用 3 楼 benben_tong 的回复:]

引用 1 楼 acherat 的回复:

SQL code

--PropTime Props PropsCoun
declare @sql varchar(8000)
set @sql = 'select convert(varchar(10),PropTime,120) PropTime'
select @sql = @sql + ',sum(case when convert……
[/Quote]
按时间分组的行转列，小三写得应该没有问题呀。

勿勿 2012-02-20

打赏
举报

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 @str varchar(max)

set @str = 'select convert(varchar(10),PropTime,120) PropTime'

select @str = @str + ',sum(case when Props='''+Props+''' then PropsCoun else 0 end) ['+Props+']'

from(select Props from Hong_Props group by Props ) t

select @str = @str + ' from Hong_Props group by  PropTime'

exec(@str)





PropTime   道具A         道具B         道具C         道具D         道具E

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

2012-02-11 24          15          0           0           0

2012-02-12 0           0           14          2           0

2012-02-13 0           0           0           50          0

2012-02-14 0           9           0           0           0

2012-02-15 20          0           0           0           10



(5 行受影响)

benben_tong 2012-02-20

打赏
举报

[Quote=引用 1 楼 acherat 的回复:]

SQL code

--PropTime Props PropsCoun
declare @sql varchar(8000)
set @sql = 'select convert(varchar(10),PropTime,120) PropTime'
select @sql = @sql + ',sum(case when convert(varchar(10),PropTime,120)=……
[/Quote]

嗯，你看问题要求了、、新手求解、

勿勿 2012-02-20

打赏
举报

好像做过类是的帖子
、
http://topic.csdn.net/u/20120217/11/cb980785-7fe5-4e64-a939-312759193221.html

AcHerat 2012-02-20

打赏
举报



--PropTime Props PropsCoun

declare @sql varchar(8000)

set @sql = 'select convert(varchar(10),PropTime,120) PropTime'

select @sql = @sql + ',sum(case when convert(varchar(10),PropTime,120)='''+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

select @sql = @sql + ' from Hong_Props group by convert(varchar(10),PropTime,120)'

exec(@sql)