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

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,...........

...全文

93 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