SQL to repeat rows from a specific year to current year

Question

I have a following table,

Code    Desc    Curr_Year
-------------------------
AB      XYZ     2019

And I am looking for a SQL query which repeats the same row from year 1993 to 2019.

The output should look like this:

Code    Desc    Curr_Year Inc_Yr
---------------------------------
AB      XYZ     2019      1993
AB      XYZ     2019      1994
AB      XYZ     2019      1995
AB      XYZ     2019      1996
.       .       .         .
.       .       .         .

AB      XYZ     2019      2019

I am using SQL Server 2012.

I am not a SQL expert and really struggling to get this done.

Any help would be greatly appreciated.

Write a recursive cte that returns the wanted years. Cross join. — jarlh, Mar 19 '19 at 15:12

score 1 · Answer 1 · answered Mar 19 '19 at 15:21

1

SQL DEMO

;WITH Years AS
(
  SELECT Inc_Yr = ROW_NUMBER() OVER (ORDER BY [object_id]) 
  FROM sys.all_objects     
)
SELECT Table1.*, Inc_Yr 
FROM Years 
CROSS JOIN Table1
WHERE Inc_Yr BETWEEN 1993  AND 2019
ORDER BY Inc_Yr;

answered Mar 19 '19 at 15:21

Juan Carlos Oropeza

47,252
12
78
118

Esteban P. · Accepted Answer · 2019-03-19T15:32:24.727

0

I would do it like:

SELECT Code
      ,Desc
      ,Curr_Year 
      ,g.num as Inc_Yr
  FROM yourtable A
  CROSS JOIN (SELECT TOP 26 1992+ ROW_NUMBER() OVER (ORDER BY [object_id]) as num FROM sys.all_objects) g

In a first step generating a list of values of all years you need, then a simple CROSS JOIN.

If you are not allowed to use the table sys.all_objects, you could still use a list of values. Have a look at the second answer to this question:

How can I select from list of values in SQL Server

edited Mar 19 '19 at 15:32

answered Mar 19 '19 at 15:17

Esteban P.

2,789
2
27
43

Thanks for the prompt response Esteban. But is there a way this can be achieved in a only 'Select' statement without declaring any variable as I have to use the query in an application. – Aniket Mar 19 '19 at 15:23
You can always use parameterized queries in your application. – Juan Carlos Oropeza Mar 19 '19 at 15:24
I am using SAP reporting application which accepts only free hand sql (which is only select statements). – Aniket Mar 19 '19 at 15:32
@Aniket I've just updated my answer to a solution without CTE – Esteban P. Mar 19 '19 at 15:32
Thats great Esteban. Thank you very much. It does solved my issue :) – Aniket Mar 19 '19 at 15:37

SQL to repeat rows from a specific year to current year

2 Answers2