求个 行转列 的语句

xzpanyao 2011-03-07 02:04:33
select Num,Time from 
(select count(Time) as Num ,(convert(varchar(30),Time,23)) as Time from Info
where From ='Ba'
and Time between DateAdd("ww",-1,getdate()) and getdate()
Group by (convert(varchar(30),Time,23))) as table1


显示结果为:
Num Time
2 2011-03-06
3 2011-03-07


我想把这个结果变成:
2011-03-06 2011-03-07
2 3

怎么写语句呢?

...全文
69 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
AcHerat 2011-03-07

declare @sql varchar(max)

select count(Time) as Num ,(convert(varchar(30),Time,23)) as Time
into #t
from Info
where From ='Ba'
and Time between DateAdd("ww",-1,getdate()) and getdate()
Group by (convert(varchar(30),Time,23))

select @sql = isnull(@sql + ',','') + '(case time when ''' + time + ''' then num else 0 end)[' + time + ']'
from (select distinct time from #t)t
exec('select ' + @sql + ' from #t')

drop table #t
回复
xzpanyao 2011-03-07

select
max(case VisitTime when '2011-03-06' then num end) as '2011-03-06' ,
max(case VisitTime when '2011-03-07' then num end) as '2011-03-07',
max(case VisitTime when '2011-03-09' then num end) as '2011-03-09'
from sdf1


VisitTime 是动态的,该怎么写啊? 不是固定的日期
回复
xzpanyao 2011-03-07
[Quote=引用 7 楼 xiao_ai_mei 的回复:]
引用 6 楼 xzpanyao 的回复:
引用 5 楼 acherat 的回复:
SQL code

declare @sql varchar(max)

select count(Time) as Num ,(convert(varchar(30),Time,23)) as Time
into #t
from Info
where From ='Ba'
and Time be……
[/Quote]

那我怎么在数据库里面找不到这个表呢,而且刚才那个代码,运行不起来
回复
Xiao_Ai_Mei 2011-03-07
[Quote=引用 6 楼 xzpanyao 的回复:]
引用 5 楼 acherat 的回复:
SQL code

declare @sql varchar(max)

select count(Time) as Num ,(convert(varchar(30),Time,23)) as Time
into #t
from Info
where From ='Ba'
and Time between DateAdd("ww",-1,……
[/Quote] 你太聪明了,对的
回复
xzpanyao 2011-03-07
[Quote=引用 5 楼 acherat 的回复:]
SQL code

declare @sql varchar(max)

select count(Time) as Num ,(convert(varchar(30),Time,23)) as Time
into #t
from Info
where From ='Ba'
and Time between DateAdd("ww",-1,getdate()) and……
[/Quote]

老大,select count(Time) as Num ,(convert(varchar(30),Time,23)) as Time
into #t
这里面 into #t是什么意思啊? 插入到一个新表里面吗?
回复
AcHerat 2011-03-07

declare @sql varchar(max)

select count(Time) as Num ,(convert(varchar(30),Time,23)) as Time
into #t
from Info
where From ='Ba'
and Time between DateAdd("ww",-1,getdate()) and getdate()
Group by (convert(varchar(30),Time,23))

select @sql = isnull(@sql + ',','') + ',(case time when ''' + time + ''' then num else 0 end)[' + time + ']'
from (select distinct time from #t)t
exec('select ' + @sql + ' from #t')
回复
xzpanyao 2011-03-07
搜了一下,不太能看明白撒。。我上面那个日期不是固定的,也是根据数据库分组得到的啊
回复
xzpanyao 2011-03-07
咋写啊,能给个例子吗。。。

[Quote=引用 1 楼 acherat 的回复:]
1# case when
2# 拼动态语句!
[/Quote]
回复
AcHerat 2011-03-07
CSDN搜索下,例子很多很多,基本每周都有人问行转列的问题!
回复
AcHerat 2011-03-07
1# case when
2# 拼动态语句!
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2011-03-07 02:04
社区公告
暂无公告