select TC001,TC002,TC003,TC004,TC005,TC006 from COPTC where convert(varchar(7),时间字段,120) = '2007-11'
---0711月 那一列是存放时间的: use test go --TC001-TC062 --时间列为datetime declare @a nvarchar(2),@i int,@s nvarchar(4000) select @a='TC',@i=1,@s='' while @i!>62 select @s=@s+','+@a+right('000'+rtrim(@i),3),@i=@i+1 set @s=stuff(@s,1,1,'') exec('select '+@s+' from T where convert(varchar(4),时间列,12)=''0711''')
--参考中国风_燃烧你的激情!!! create function fn() returns table as return ( --TC001-TC062 --时间列为datetime declare @a nvarchar(2),@i int,@s nvarchar(4000) select @a='TC',@i=1,@s='' while @i!>62 select @s=@s+','+@a+right('000'+rtrim(@i),3),@i=@i+1 set @s=stuff(@s,1,1,'') exec('select '+@s+' from T where convert(varchar(4),时间列,12)=''0711''') )--结果 select * from dbo.fn()
select [显示]=TC001-TC062
from
T
那一列是存放时间的:
use test
go
--TC001-TC062
--时间列为datetime
declare @a nvarchar(2),@i int,@s nvarchar(4000)
select @a='TC',@i=1,@s=''
while @i!>62
select @s=@s+','+@a+right('000'+rtrim(@i),3),@i=@i+1
set @s=stuff(@s,1,1,'')
exec('select '+@s+' from T where convert(varchar(4),时间列,12)=''0711''')
create function fn()
returns table
as
return
(
--TC001-TC062
--时间列为datetime
declare @a nvarchar(2),@i int,@s nvarchar(4000)
select @a='TC',@i=1,@s=''
while @i!>62
select @s=@s+','+@a+right('000'+rtrim(@i),3),@i=@i+1
set @s=stuff(@s,1,1,'')
exec('select '+@s+' from T where convert(varchar(4),时间列,12)=''0711''')
)--结果
select * from dbo.fn()