如何根据这样的查询结果创建一个表?

xuaimin0402 2009-04-06 04:16:18
select distinct kqrq from YBC200904
2009-03-26
2009-03-27
2009-03-28
2009-03-29
2009-03-30
2009-03-31
2009-04-01
2009-04-02
2009-04-03
2009-04-04
2009-04-05
2009-04-06
2009-04-07
2009-04-08
2009-04-09
2009-04-10
2009-04-11
2009-04-12
2009-04-13
2009-04-14
2009-04-15
2009-04-16
2009-04-17
2009-04-18
2009-04-19
2009-04-20
2009-04-21
2009-04-22
2009-04-23
2009-04-24
2009-04-25

我想创建一个表它的列是name,no,2009-03-26,2009-03-27,2009-03-28,2009-03-29,2009-03-30,...........
...全文
103 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
lifenglijun 2009-04-06
  • 打赏
  • 举报
回复
写个事物递归
xuaimin0402 2009-04-06
  • 打赏
  • 举报
回复
看看我的语句可以实现吗?写了有点乱,不知道思路是否正确,请大事指点啊!

ALTER PROCEDURE get_chuqin
(@tbname char(6) = '200807')
AS
BEGIN
set @tbname = 'YBC'+@tbname
declare @sql nvarchar(8000)
declare @no char(10)
set @sql='declare cus cursor for select distinct kqrq from '+@tbname+ ' open cus fetch next from cus into '+@no
set @sql=@sql+'while (@@fetch_status=0) begin set '
@sql=@sql+'creat table tbtest(name char(10),no varchar(10),bmno char(10),bmmc char(20),'+@no+' char(255) fetch next from cus into '+@no+' end close cus
deallocate cus'
execute (@sql)

END
xuaimin0402 2009-04-06
  • 打赏
  • 举报
回复
怎么实现啊?如果用游标呢?
我的YBC200904也是变动的,如何实现啊?
[Quote=引用 2 楼 qianjin036a 的回复:]
用动态查询语句.
[/Quote]
Zoezs 2009-04-06
  • 打赏
  • 举报
回复

insert into #temp
select '2009-03-27' union all
select '2009-03-28' union all
select '2009-03-29' union all
select '2009-03-30' union all
select '2009-03-31' union all
select '2009-04-01' union all
select '2009-04-02' union all
select '2009-04-03'

declare @sql varchar(8000)
set @sql='create table tb(name varchar(50),no int'
select @sql=@sql+',['+date+'] varchar(50)'
from (select distinct date from #temp) t
select @sql=@sql+')'
print @sql
exec(@sql)



create table tb(name varchar(50),no int,[2009-03-26] varchar(50),[2009-03-27] varchar(50),[2009-03-28] varchar(50),[2009-03-29] varchar(50),[2009-03-30] varchar(50),[2009-03-31] varchar(50),[2009-04-01] varchar(50),[2009-04-02] varchar(50),[2009-04-03] varchar(50))

htl258_Tony 2009-04-06
  • 打赏
  • 举报
回复
create table YBC200904(kqrq datetime)
insert YBC200904 select '2009-03-26'
insert YBC200904 select '2009-03-27'
insert YBC200904 select '2009-03-28'
insert YBC200904 select '2009-03-29'
insert YBC200904 select '2009-03-30'
insert YBC200904 select '2009-03-31'
insert YBC200904 select '2009-04-01'
insert YBC200904 select '2009-04-02'
insert YBC200904 select '2009-04-03'
insert YBC200904 select '2009-04-04'
insert YBC200904 select '2009-04-05'
insert YBC200904 select '2009-04-06'
insert YBC200904 select '2009-04-07'
insert YBC200904 select '2009-04-08'
insert YBC200904 select '2009-04-09'
insert YBC200904 select '2009-04-10'
insert YBC200904 select '2009-04-11'
insert YBC200904 select '2009-04-12'
insert YBC200904 select '2009-04-13'
insert YBC200904 select '2009-04-14'
insert YBC200904 select '2009-04-15'
insert YBC200904 select '2009-04-16'
insert YBC200904 select '2009-04-17'
insert YBC200904 select '2009-04-18'
insert YBC200904 select '2009-04-19'
insert YBC200904 select '2009-04-20'
insert YBC200904 select '2009-04-21'
insert YBC200904 select '2009-04-22'
insert YBC200904 select '2009-04-23'
insert YBC200904 select '2009-04-24'
insert YBC200904 select '2009-04-25'
go

declare @s varchar(8000)
select @s='create table tbtest(name varchar(10),no varchar(10)'
select @s=@s+',['+convert(varchar(10),kqrq,120)+'] varchar(10)'
from (select distinct kqrq from YBC200904) t
select @s=@s+')'
exec(@s)

select * from tbtest
/*
name no 2009-03-26 2009-03-27 2009-03-28 2009-03-29 2009-03-30 2009-03-31 2009-04-01 2009-04-02 2009-04-03 2009-04-04 2009-04-05 2009-04-06 2009-04-07 2009-04-08 2009-04-09 2009-04-10 2009-04-11 2009-04-12 2009-04-13 2009-04-14 2009-04-15 2009-04-16 2009-04-17 2009-04-18 2009-04-19 2009-04-20 2009-04-21 2009-04-22 2009-04-23 2009-04-24 2009-04-25
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

(0 行受影响)
*/

drop table tbtest
htl258_Tony 2009-04-06
  • 打赏
  • 举报
回复
declare @s varchar(8000)
select @s='create table tb(name varchar(10)'
select @s=@s+',['+convert(varchar(10),kqrq,120)+'] varchar(10)'
from (select distinct kqrq from YBC200904) t
select @s=@s+')'
exec(@s)
yygyogfny 2009-04-06
  • 打赏
  • 举报
回复

declare @sql nvarchar(4000)
set @sql = ''
select @sql = @sql + ' max(case when kqrq = ''' + kqrq + ''' then kqrq else 0 end as [' + kqrq +'],'
from YBC200904 group by kqrq

set @sql = ' select name,no ' + left(@sql,len(@sql)-1) + 'from YBC200904 group by name,no'

exec(@sql
-晴天 2009-04-06
  • 打赏
  • 举报
回复
用动态查询语句.
Zoezs 2009-04-06
  • 打赏
  • 举报
回复
你的意思是每个日期都要做为一个列名?

22,210

社区成员

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

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