在SQLserver中我构造了一个表,里面存放的是某个人每天的工作量,表结构如下:
Name Date Number
001 2005/12/1 50
001 2005/12/2 70
001 2005/12/3 80
002 2005/12/1 45
002 2005/12/2 55
......但在页面显示(ASP.NET)的时候,我想这样显示:
2005/12/1 2005/12/2 2005/12/3
001 50 70 80
002 45 55 0 请问这可以实现吗,我的表结构是否合理?请高手指点,在线等:
Name Date Number
001 2005/12/1 50
001 2005/12/2 70
001 2005/12/3 80
002 2005/12/1 45
002 2005/12/2 55
......但在页面显示(ASP.NET)的时候,我想这样显示:
2005/12/1 2005/12/2 2005/12/3
001 50 70 80
002 45 55 0 请问这可以实现吗,我的表结构是否合理?请高手指点,在线等:
Select * from TDemo 内容为:
Name Date Number
001 2005/12/1 50
001 2005/12/2 70
001 2005/12/3 80
002 2005/12/1 45
002 2005/12/2 55运行:
Declare @Sql nvarchar(4000)
Set @Sql = 'Select Name'
Select @Sql = @Sql + ', sum(case when Date = ''' + Date + ''' then number else 0 end) as ''' + Date + ''''
from (select distinct Date from TDemo) as A
Set @Sql = @Sql + ' from TDemo group by Name'
Exec(@Sql)
Name 2005-12-01 2005-12-02 2005-12-03
-------------------------------------------------- ----------- ----------- -----------
001 50 70 80
002 45 55 0
set @s = ''select
@s=@s+',['+convert(char(10),[Date],111)+']=max(case when [Date]='''+convert(char(10),[Date],120)+''' then Number else 0 end)'
from
tabname
group by
[Date]
ordre by
[Date]set @s='select Name,'+@s+' from tabname group by Name order by Name'exec(@s)
create table tabname(Name varchar(20),Date datetime,Number int)
insert into tabname select '001','2005/12/1',50
insert into tabname select '001','2005/12/2',70
insert into tabname select '001','2005/12/3',80
insert into tabname select '002','2005/12/1',45
insert into tabname select '002','2005/12/2',55
go--执行动态交叉表查询
declare @s varchar(8000)
set @s = ''select
@s=@s+',['+convert(char(10),[Date],111)+']=max(case when [Date]='''+convert(char(10),[Date],120)+''' then Number else 0 end)'
from
tabname
group by
[Date]
order by
[Date]set @s='select Name'+@s+' from tabname group by Name order by Name'exec(@s)
go--输出结果
/*
Name 2005/12/01 2005/12/02 2005/12/03
--------------------------------------------------
001 50 70 80
002 45 55 0
*/--删除测试数据
drop table tabname
go
根据输入的时间段,做个游标,动态创建列名,不要忘了日期类型的前后加[],然后select临时表即可!
--libin_ftsafe(子陌红尘|潇湘剑公子@dev-club)的方法应该加上sum
--生成测试数据
create table tabname(Name varchar(20),Date datetime,Number int)
insert into tabname select '001','2005/12/1',50
insert into tabname select '001','2005/12/2',70
insert into tabname select '001','2005/12/3',80
insert into tabname select '002','2005/12/1',45
insert into tabname select '002','2005/12/2',55
insert into tabname select '002','2005/12/1',45
insert into tabname select '002','2005/12/2',55
go--执行动态交叉表查询
declare @s varchar(8000)
set @s = ''select
@s=@s+',['+convert(char(10),[Date],111)+']=max(case when [Date]='''+convert(char(10),[Date],120)+''' then Number else 0 end)'
from
tabname
group by
[Date]
order by
[Date]
set @s='select Name'+@s+' from tabname group by Name order by Name'
exec(@s)
----------result-------------------------
Name 2005/12/01 2005/12/02 2005/12/03
-------------------- ----------- ----------- -----------
001 50 70 80
002 45 55 0
---使用sum后---------------------
Declare @Sql nvarchar(4000)
Set @Sql = 'Select Name'
Select @Sql = @Sql + ', sum(case when Date =''' + convert(char(10),[Date],120) + ''' then Number else 0 end) as ''' + convert(char(10),[Date],120) + ''''
from (select distinct Date from tabname) as A
Set @Sql = @Sql + ' from tabname group by Name'
exec (@sql)
-----result--------------------
Name 2005-12-01 2005-12-02 2005-12-03
-------------------- ----------- ----------- -----------
001 50 70 80
002 90 110 0
drop table tabname
ljz9425(阿九) 的不行啊 SQL Server-----------------------------------------------------
字段Date类型为varchar(10),
如果你用datetime类型, 用如下:
Declare@Sql nvarchar(4000)
Set @Sql = 'Select Name'
Select @Sql = @Sql + ', sum(case when Date = ''' + cast(Date as varchar(10)) + ''' then number else 0 end) as ''' + cast(Date as varchar(10))+ ''''
from (select distinct Date from TDemo) as A
Set @Sql = @Sql + ' from TDemo group by Name'
Exec(@Sql)
Declare@Sql nvarchar(4000)
Set @Sql = 'Select Name'
Select @Sql = @Sql + ', sum(case when cast(Date as varchar(10)) = ''' + cast(Date as varchar(10)) + ''' then number else 0 end) as ''' + cast(Date as varchar(10))+ ''''
from (select distinct Date from TDemo) as A
Set @Sql = @Sql + ' from TDemo group by Name'
Exec(@Sql)注:把date字段转换成字符类型
2005-2-21 2005-2-21
12 13
create table tabname(Name varchar(20),Date datetime,Number int)
insert into tabname select '001','2005/12/1',50
insert into tabname select '001','2005/12/2',70
insert into tabname select '001','2005/12/3',80
insert into tabname select '001','2005/12/1',45
insert into tabname select '001','2005/12/2',55
goselect identity(int,1,1) as id,* into # from tabname--执行动态交叉表查询
declare @s varchar(8000)
set @s = ''select
@s=@s+',['+convert(char(10),[Date],111)+']=max(case when [Date]='''+convert(char(10),[Date],120)+''' and id='+rtrim(id)+' then Number else 0 end)'
from
#
group by
id,[Date]
order by
id,[Date]set @s='select Name'+@s+' from # group by Name order by Name'
exec(@s)
go--输出结果
/*
Name 2005/12/01 2005/12/02 2005/12/03 2005/12/01 2005/12/02
-------------------- ----------- ----------- ----------- ----------- -----------
001 50 70 80 45 55
*/--删除测试数据
drop table tabname,#
go
A B C
1 a a1
1 b b1
1 c c1
1 d d1
2 a a2
2 e e1
2 b b1
2 d d1
现在希望将其显示如果下结构:
A a b c d
1 a1 b1 c1 d1
2 a2 e1 b1 d1
让它这么实现可能吗?跪求!
------------------------------------------------------------------------------
上面的列名与数据之间有没有严格的对应关系?还是顺序列出即可?