SQL行转列、、、、急，急。正确答案，分数全部是你的。
现有表【hong_prop】，里面的字段为以下这些：
游戏类型<gametype>,游戏区<arrea>，时间<regtime>,金币类型<type>,道具名称<Propname>,销售次数<cvount>
表中的测试数据为 ：
gametype arrea regtime type Propname count
1 12011-12-22 绑定金币 二档绑定石 23
1 12011-12-21 绑定金币 极品的物攻宝石 11
1 12011-12-20 绑定金币 度世丹 1
112011-12-19 绑定金币 二档绑定石 2
112011-12-18 绑定金币 极品的物攻宝石 10
112011-12-18 绑定金币 六档升星石 3
112011-12-17 绑定金币 极品的物攻宝石 13
112011-12-17 绑定金币 六档升星石 15
112011-12-17 绑定金币 极品的物攻宝石 1
112011-12-16 绑定金币 度世丹 2
112011-12-16 绑定金币 二档绑定石 14
112011-12-15 绑定金币 六档升星石 1
112011-12-14 绑定金币 度世丹 5
112011-12-14 绑定金币 度世丹 6
112011-12-14 绑定金币 二档绑定石 8
112011-12-14 绑定金币 二档绑定石 10
112011-12-13 绑定金币 极品的物攻宝石 11
112011-12-12 绑定金币 二档绑定石 12
现在我需要一条SQL语句，查询出每天销售的道具情况。用时间作为行，商城道具作为列，且只查询最近一周的数据；
想要的最后结果是这样子的：
名称 2011-12-22 2011-12-21 2011-12-20 2011-12-19 2011-12-18 2011-12-17 2011-12-16
度世丹 0 0 1 0 0 0 2
二档绑定石 23 0 0 2 0 0 14
六档升星石 0 0 0 0 3 15 14
极品的物攻宝石 0 11 0 0 10 14 0
谢啦，语句正确，分数全部归你。
[解决办法]
示例：
select Propname,sum(case when arrea='2011-12-22' then count end),
sum(case when arrea='2011-12-21' then count end),
...
sum(case when arrea='2011-12-16' then count end)
from tt group by Propname
[解决办法]
示例：
假设有张学生成绩表(CJ)如下
Name Subject Result
张三 语文 80
张三
数学 90
张三 物理 85
李四 语文 85
李四 数学
92
李四 物理 82
想变成
姓名 语文 数学 物理
张三 80 90 85
李四 85 92
82
declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql
+ ',sum(case Subject when '''+Subject+''' then Result end)
['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql =
@sql+' from test group by name'
exec(@sql)
自行修改
[解决办法]
declare @sql varchar(4000);
set @sql = 'select Propname';
select @sql = @sql
+ ',sum(case when regtime='''+aa+''' then count else 0 end) as
['+aa+']'
from (select distinct CONVERT(CHAR(10),regtime,120) as aa from hong_prop) as a;
select @sql =
@sql+' from hong_prop group by Propname';
select @sql;
exec(@sql);
[解决办法]
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([gametype] int,[arrea] int,[regtime] date,[type] varchar(8),[Propname] varchar(14),[count] int)goinsert [test]select 1,1,'2011-12-22','绑定金币','二档绑定石',23 union allselect 1,1,'2011-12-21','绑定金币','极品的物攻宝石',11 union allselect 1,1,'2011-12-20','绑定金币','度世丹',1 union allselect 1,1,'2011-12-19','绑定金币','二档绑定石',2 union allselect 1,1,'2011-12-18','绑定金币','极品的物攻宝石',10 union allselect 1,1,'2011-12-18','绑定金币','六档升星石',3 union allselect 1,1,'2011-12-17','绑定金币','极品的物攻宝石',13 union allselect 1,1,'2011-12-17','绑定金币','六档升星石',15 union allselect 1,1,'2011-12-17','绑定金币','极品的物攻宝石',1 union allselect 1,1,'2011-12-16','绑定金币','度世丹',2 union allselect 1,1,'2011-12-16','绑定金币','二档绑定石',14 union allselect 1,1,'2011-12-15','绑定金币','六档升星石',1 union allselect 1,1,'2011-12-14','绑定金币','度世丹',5 union allselect 1,1,'2011-12-14','绑定金币','度世丹',6 union allselect 1,1,'2011-12-14','绑定金币','二档绑定石',8 union allselect 1,1,'2011-12-14','绑定金币','二档绑定石',10 union allselect 1,1,'2011-12-13','绑定金币','极品的物攻宝石',11 union allselect 1,1,'2011-12-12','绑定金币','二档绑定石',12godeclare @str varchar(max)set @str=''select @str=@str+',['+CONVERT(varchar(10),[regtime],120)+']=max(case when [regtime]=' +QUOTENAME([regtime],'''')+' then [count] else 0 end)'from test group by [regtime]order by [regtime] descexec('select [Propname] as 名称'+@str+' from test group by[Propname]')go/*名称 2011-12-22 2011-12-21 2011-12-20 2011-12-19 2011-12-18 2011-12-17 2011-12-16 2011-12-15 2011-12-14 2011-12-13 2011-12-12--------------------------------------------------------------------------------度世丹 0 0 1 0 0 0 2 0 6 0 0二档绑定石 23 0 0 2 0 0 14 0 10 0 12极品的物攻宝石 0 11 0 0 10 13 0 0 0 11 0六档升星石 0 0 0 0 3 15 0 1 0 0 0*/
[解决办法]
--> 测试数据: [hong_prop]if object_id('[hong_prop]') is not null drop table [hong_prop]create table [hong_prop] (gametype int,arrea int,regtime datetime,type varchar(8),Propname varchar(14),count int)insert into [hong_prop]select 1,1,'2011-12-22','绑定金币','二档绑定石',23 union allselect 1,1,'2011-12-21','绑定金币','极品的物攻宝石',11 union allselect 1,1,'2011-12-20','绑定金币','度世丹',1 union allselect 1,1,'2011-12-19','绑定金币','二档绑定石',2 union allselect 1,1,'2011-12-18','绑定金币','极品的物攻宝石',10 union allselect 1,1,'2011-12-18','绑定金币','六档升星石',3 union allselect 1,1,'2011-12-17','绑定金币','极品的物攻宝石',13 union allselect 1,1,'2011-12-17','绑定金币','六档升星石',15 union allselect 1,1,'2011-12-17','绑定金币','极品的物攻宝石',1 union allselect 1,1,'2011-12-16','绑定金币','度世丹',2 union allselect 1,1,'2011-12-16','绑定金币','二档绑定石',14 union allselect 1,1,'2011-12-15','绑定金币','六档升星石',1 union allselect 1,1,'2011-12-14','绑定金币','度世丹',5 union allselect 1,1,'2011-12-14','绑定金币','度世丹',6 union allselect 1,1,'2011-12-14','绑定金币','二档绑定石',8 union allselect 1,1,'2011-12-14','绑定金币','二档绑定石',10 union allselect 1,1,'2011-12-13','绑定金币','极品的物攻宝石',11 union allselect 1,1,'2011-12-12','绑定金币','二档绑定石',12go--获取最近一周的时间declare @prevsunday datetime--设置周1为每周的第一天set datefirst 1--获取上周日select @prevsunday=dateadd(dd,-DATEPART(dw,GETDATE()),GETDATE())--获取到上周一到上周日--select dt=dateadd(dd,-number,@prevsunday) from master..spt_values where type='p' and number between 0 and 6declare @sql varchar(8000)set @sql='select Propname'select @sql=@sql+',['+CONVERT(varchar(10),dt,120)+']=sum(case CONVERT(varchar(10),regtime,120) when '''+CONVERT(varchar(10),dt,120)+'''then [count] else 0 end)'from (select dt=dateadd(dd,-number,@prevsunday) from master..spt_values where type='p' and number between 0 and 6)aset @sql=@sql+' from [hong_prop] group by Propname'exec(@sql)--表中没有最近一周的信息，所以结果：Propname 2012-06-24 2012-06-23 2012-06-22 2012-06-21 2012-06-20 2012-06-19 2012-06-18-------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------度世丹 0 0 0 0 0 0 0二档绑定石 0 0 0 0 0 0 0极品的物攻宝石 0 0 0 0 0 0 0六档升星石 0 0 0 0 0 0 0
[解决办法]
--不知道楼主表示的最近一周是指的什么，这个是获取销售日期最后7天的数据统计declare @sql varchar(8000)set @sql='select Propname'select @sql=@sql+',['+CONVERT(varchar(10),regtime,120)+']=sum(case CONVERT(varchar(10),regtime,120) when '''+CONVERT(varchar(10),regtime,120)+'''then [count] else 0 end)'from (select top 7 regtime from hong_prop group by regtime order by regtime desc)aset @sql=@sql+' from [hong_prop] group by Propname'exec(@sql)--结果：Propname 2011-12-22 2011-12-21 2011-12-20 2011-12-19 2011-12-18 2011-12-17 2011-12-16-------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------度世丹 0 0 1 0 0 0 2二档绑定石 23 0 0 2 0 0 14极品的物攻宝石 0 11 0 0 10 14 0六档升星石 0 0 0 0 3 15 0
[解决办法]
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([gametype] int,[arrea] int,[regtime] date,[type] varchar(8),[Propname] varchar(14),[count] int)goinsert [test]select 1,1,'2011-12-22','绑定金币','二档绑定石',23 union allselect 1,1,'2011-12-21','绑定金币','极品的物攻宝石',11 union allselect 1,1,'2011-12-20','绑定金币','度世丹',1 union allselect 1,1,'2011-12-19','绑定金币','二档绑定石',2 union allselect 1,1,'2011-12-18','绑定金币','极品的物攻宝石',10 union allselect 1,1,'2011-12-18','绑定金币','六档升星石',3 union allselect 1,1,'2011-12-17','绑定金币','极品的物攻宝石',13 union allselect 1,1,'2011-12-17','绑定金币','六档升星石',15 union allselect 1,1,'2011-12-17','绑定金币','极品的物攻宝石',1 union allselect 1,1,'2011-12-16','绑定金币','度世丹',2 union allselect 1,1,'2011-12-16','绑定金币','二档绑定石',14 union allselect 1,1,'2011-12-15','绑定金币','六档升星石',1 union allselect 1,1,'2011-12-14','绑定金币','度世丹',5 union allselect 1,1,'2011-12-14','绑定金币','度世丹',6 union allselect 1,1,'2011-12-14','绑定金币','二档绑定石',8 union allselect 1,1,'2011-12-14','绑定金币','二档绑定石',10 union allselect 1,1,'2011-12-13','绑定金币','极品的物攻宝石',11 union allselect 1,1,'2011-12-12','绑定金币','二档绑定石',12godeclare @date dateselect @date=MAX([regtime]) from test declare @str varchar(max)set @str=''select @str=@str+',['+CONVERT(varchar(10),[regtime],120)+']=max(case when [regtime]=' +QUOTENAME([regtime],'''')+' then [count] else 0 end)'from test where DATEDIFF(DD,[regtime],@date)<=6group by [regtime]order by [regtime] descexec('select [Propname] as 名称'+@str+' from test group by[Propname]')go/*名称 2011-12-22 2011-12-21 2011-12-20 2011-12-19 2011-12-18 2011-12-17 2011-12-16度世丹 0 0 1 0 0 0 2二档绑定石 23 0 0 2 0 0 14极品的物攻宝石 0 11 0 0 10 13 0六档升星石 0 0 0 0 3 15 0*/--楼主要找的就是最大日期往前推七天的数据吧