SQL如何从单个行创建多行(SQL how to create multiple row from

您好我是新来的SQL Server 2008

我想扩大基于另一个colomn单排多行，

如

date          value
7-2011         5

结果：

2011-07-01     
2011-08-01
2011-09-01
2011-10-01
2012-11-01

日期shoild是当前和下个月的第一天，重复5次

Answer 1:

尝试：

DECLARE @YourTable table (YourDate datetime, value int)

insert into @YourTable VALUES ('2011-7-12',5)

;WITH AllNumbers AS
(
    SELECT 0 AS Number
    UNION ALL
    SELECT Number+1
        FROM AllNumbers
        WHERE Number<4
)
SELECT 
    dateadd(month,number,DATEADD(month,DATEDIFF(month,0,YourDate),0))
    FROM @YourTable        y
    INNER JOIN AllNumbers  a ON 1=1

输出：

-----------------------
2011-07-01 00:00:00.000
2011-08-01 00:00:00.000
2011-09-01 00:00:00.000
2011-10-01 00:00:00.000
2011-11-01 00:00:00.000

(5 row(s) affected)

它与表中的多行，在这里看到：

DECLARE @YourTable table (YourDate datetime, ValueOf int)

insert into @YourTable VALUES ('2011-7-12',5)
insert into @YourTable VALUES ('2012-4-24',6)

;WITH AllNumbers AS
(
    SELECT 0 AS Number
    UNION ALL
    SELECT Number+1
        FROM AllNumbers
        WHERE Number<4
)
SELECT 
    y.ValueOf
        ,dateadd(month,number,DATEADD(month,DATEDIFF(month,0,y.YourDate),0))
    FROM @YourTable        y
    INNER JOIN AllNumbers  a ON 1=1
    ORDER BY 1,2

OUTPUT：

ValueOf     
----------- -----------------------
5           2011-07-01 00:00:00.000
5           2011-08-01 00:00:00.000
5           2011-09-01 00:00:00.000
5           2011-10-01 00:00:00.000
5           2011-11-01 00:00:00.000
6           2012-04-01 00:00:00.000
6           2012-05-01 00:00:00.000
6           2012-06-01 00:00:00.000
6           2012-07-01 00:00:00.000
6           2012-08-01 00:00:00.000

(10 row(s) affected)

另外，我没有SQL Server 2008中可用，所以我用日期时间，如果你有2008，你可以使用DATE数据类型，你不必地板的日期时间，所以使用这行：

dateadd(month,number,y.YourDate)

Answer 2:

create  function addMonths(@date date, @limit int)
returns @date_table TABLE(
myDate date
)
AS
begin
  declare @cont int
  set @cont = 1
  while (@cont <= @limit) begin
    insert into @date_table values(DATEADD(MONTH,@cont,@date))
    set @cont=@cont+1
  end

  return 
end

用法：

select * from addMonths(GETDATE(),5)

编辑：

create table mydates(
myDate date,
inc_months int)


insert into mydates values ('01/01/2005',3)
insert into mydates values ('01/01/2006',5)


select AM.mydate 
from mydates MD cross apply addMonths(MD.mydate,MD.inc_months) AM

结果：

2005-02-01
2005-03-01
2005-04-01
2006-02-01
2006-03-01
2006-04-01
2006-05-01
2006-06-01

文章来源: SQL how to create multiple row from a single row