已知一个参数包含多个ID，一张表是一年12个月，怎么生成一张表让每个ID都有这12个月

[telelogic1]

已知有参数
declare @ids varchar(8000)
set @ids='''011,'',''012'',''013'''

已知有表a 存放日期

表a: (表a是固定的也是已知的，有这张表，因为年份可能不一样)

create table a (month varchar(20))
insert into a values ('2005-01')
insert into a values ('2005-02')
insert into a values ('2005-03')
insert into a values ('2005-04')
insert into a values ('2005-05')
insert into a values ('2005-06')
insert into a values ('2005-07')
insert into a values ('2005-08')
insert into a values ('2005-09')
insert into a values ('2005-10')
insert into a values ('2005-11')
insert into a values ('2005-12')


表a的数据:

month
'2005-01'
'2005-02'
'2005-03'
'2005-04'
'2005-05'
'2005-06'
'2005-07'
'2005-08'
'2005-09'
'2005-10'
'2005-11'
'2005-12'



要得到的结果:

011 '2005-01'
011 '2005-02'
011 '2005-03'
011 '2005-04'
011 '2005-05'
011 '2005-06'
011 '2005-07'
011 '2005-08'
011 '2005-09'
011 '2005-10'
011 '2005-11'
011 '2005-12'



012 '2005-01'
012 '2005-02'
012 '2005-03'
012 '2005-04'
012 '2005-05'
012 '2005-06'
012 '2005-07'
012 '2005-08'
012 '2005-09'
012 '2005-10'
012 '2005-11'
012 '2005-12'



013 '2005-01'
013 '2005-02'
013 '2005-03'
013 '2005-04'
013 '2005-05'
013 '2005-06'
013 '2005-07'
013 '2005-08'
013 '2005-09'
013 '2005-10'
013 '2005-11'
013 '2005-12'

这样的SQL该怎么弄呢？

[wutao222]

create table b (no)
insert into b values('011','012','013'..)
select no ,month from a ,b

[Frewin]

用笛卡尔积

[wuha2008]

up

[lovemsn]

頂一下

[Well]

declare @ids varchar(40)
set @ids='''011'',''012'',''013'''
declare @len int
set @len=len(@ids)-len(replace(@ids,',',''))+1
while(@len>1)
begin
select substring(@ids,2,patindex('%,%',@ids)-3) ID,* from A
select @ids=right(@ids,len(@ids)-patindex('%,%',@ids)),@len=@len-1
end
select substring(@ids,2,len(@ids)-2) ID,* from A