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

[xuaimin0402]

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

[Zoezs]

你的意思是每个日期都要做为一个列名？

[-晴天]

用动态查询语句.

[yygyogfny]

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

[htl258_Tony]

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)

[htl258_Tony]

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

[Zoezs]

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))

[xuaimin0402]

怎么实现啊?如果用游标呢?
我的YBC200904也是变动的,如何实现啊?
[Quote=引用 2 楼 qianjin036a 的回复:]
用动态查询语句.
[/Quote]

[xuaimin0402]

看看我的语句可以实现吗?写了有点乱,不知道思路是否正确,请大事指点啊!

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

[lifenglijun]

写个事物递归