求个 行转列 的语句

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

怎么写语句呢?

...全文
103 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
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# 拼动态语句!

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧