请问新增一个按月递增的列的SQL语句该怎么写

[rxzipqqbqk]

如题：

现在的表如下
YEAR ID DATE
2016 1 20070101
2016 2 20070101


希望变成如下：
YEAR YYYYMM ID DATE
2016 201401 1 20070101
2016 201402 1 20070101
...
...
2016 201612 1 20070101
2016 201401 2 20070101
2016 201402 2 20070101
...
...
2016 201612 2 20070101


即在新生成一列并按年月递增，其余字段都一样。一直没想出来该如何用SQL来写，求大神帮忙

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([YEAR] NVARCHAR(100),[ID] int,[DATE] NVARCHAR(100))
Insert #T
select '2016',1,'20070101' union all
select '2016',2,'20070101'
Go
--测试数据结束
SELECT [YEAR],CONVERT(VARCHAR(6), b.YYYYMM,112) AS YYYYMM ,[ID],[DATE]
FROM    #T
        CROSS APPLY( SELECT  DATEADD(MONTH, number,
                                    CONVERT(DATE, '2014-01-01'))  YYYYMM 
                    FROM    master.dbo.spt_values
                    WHERE   type = 'P' AND number <36
                  ) b 

[卖水果的net]

SQL> 
SQL> create table test(Y int, ID int, D varchar(10));
Table created
SQL> begin
  2      insert into test values(2016, 1, 20070101);
  3      insert into test values(2016, 2, 20070101);
  4  end;
  5  /
PL/SQL procedure successfully completed
SQL> col Y format a10;
SQL> col ID format a10;
SQL> with m as (
  2      select add_months(date'2014-01-01', rownum-1) ad
  3       from dual connect by rownum <=36
  4  )
  5  select Y, ID, to_char(ad,'yyyy-mm') ad, D from m, test
  6  order by 2, 3;
         Y         ID AD      D
---------- ---------- ------- ----------
      2016          1 2014-01 20070101
      2016          1 2014-02 20070101
      2016          1 2014-03 20070101
.................
      2016          2 2016-06 20070101
      2016          2 2016-07 20070101
      2016          2 2016-08 20070101
      2016          2 2016-09 20070101
      2016          2 2016-10 20070101
      2016          2 2016-11 20070101
      2016          2 2016-12 20070101
72 rows selected
SQL> drop table test purge;
Table dropped

SQL> 

[卖水果的net]

忽略 2#，看错板块了。。

[RINK_1]

SELECT A.*,CONVERT(VARCHAR(6),DATEADD(MM,NUMBER,'2014-01-01'),112) AS YYYYMM FROM TABLE_XXX A JOIN MASTER.DBO.SPT_VALUES B ON NUMBER<=DATEDIFF(MM,'2014-01-01',CAST(YEAR+'-12-31' AS DATETIME)) WHERE TYPE='P'

[道素]

你想要这样的数据？

declare @start int=2014

;with testdata([YEAR],ID,[DATE])AS(
    select 2016,1,'20070101' union
    select 2016,2,'20070101'
)
select t.[Year],ltrim(@start+sv.number/12)+right('00'+ltrim(sv.number%12+1),2) as YYYYMM ,t.ID,[Date]
from testdata as t
inner join master.dbo.spt_values as sv on sv.type='P' and sv.number between 0 and (t.year-@start+1)*12-1

  	Year	YYYYMM	ID	Date
1	2016	201401	1	20070101
2	2016	201402	1	20070101
3	2016	201403	1	20070101
4	2016	201404	1	20070101
5	2016	201405	1	20070101
6	2016	201406	1	20070101
7	2016	201407	1	20070101
8	2016	201408	1	20070101
9	2016	201409	1	20070101
10	2016	201410	1	20070101
11	2016	201411	1	20070101
12	2016	201412	1	20070101
13	2016	201501	1	20070101
14	2016	201502	1	20070101
15	2016	201503	1	20070101
16	2016	201504	1	20070101
17	2016	201505	1	20070101
18	2016	201506	1	20070101
19	2016	201507	1	20070101
20	2016	201508	1	20070101
21	2016	201509	1	20070101
22	2016	201510	1	20070101
23	2016	201511	1	20070101
24	2016	201512	1	20070101
25	2016	201601	1	20070101
26	2016	201602	1	20070101
27	2016	201603	1	20070101
28	2016	201604	1	20070101
29	2016	201605	1	20070101
30	2016	201606	1	20070101
31	2016	201607	1	20070101
32	2016	201608	1	20070101
33	2016	201609	1	20070101
34	2016	201610	1	20070101
35	2016	201611	1	20070101
36	2016	201612	1	20070101
37	2016	201401	2	20070101
38	2016	201402	2	20070101
39	2016	201403	2	20070101
40	2016	201404	2	20070101
41	2016	201405	2	20070101
42	2016	201406	2	20070101
43	2016	201407	2	20070101
44	2016	201408	2	20070101
45	2016	201409	2	20070101
46	2016	201410	2	20070101
47	2016	201411	2	20070101
48	2016	201412	2	20070101
49	2016	201501	2	20070101
50	2016	201502	2	20070101
51	2016	201503	2	20070101
52	2016	201504	2	20070101
53	2016	201505	2	20070101
54	2016	201506	2	20070101
55	2016	201507	2	20070101
56	2016	201508	2	20070101
57	2016	201509	2	20070101
58	2016	201510	2	20070101
59	2016	201511	2	20070101
60	2016	201512	2	20070101
61	2016	201601	2	20070101
62	2016	201602	2	20070101
63	2016	201603	2	20070101
64	2016	201604	2	20070101
65	2016	201605	2	20070101
66	2016	201606	2	20070101
67	2016	201607	2	20070101
68	2016	201608	2	20070101
69	2016	201609	2	20070101
70	2016	201610	2	20070101
71	2016	201611	2	20070101
72	2016	201612	2	20070101