CODEWITHSUNDEEP
sqlt-sql
RobbeVL
RobbeVL

Reputation: 83

Generate "missing" Rows SQL

I would like to create a View that would rearrange my current table. --> T-SQL *The table looks like this:

enter image description here

What you might notice is that the currencies are not unique per row, but a row can be about multiple years.

I would need a view that would add the missing rows.

For example :

I have not clue how to get started with this! anyone any idea ?

Upvotes: 1

Views: 603

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use a recursive CTE, but no aggregation is needed and you need to keep all the columns that you want:

with cte as (
      select kurst, fkurr, ktext, tcurr, ratefrom, rateto
      from t
      union all
      select kurst, fkurr, ktext, tcurr, ratefrom + 1, rateto
      from cte
      where ratefrom < rateto
    )
select kurst, fkur, ktext, tcurr, ratefrom as year
from cte c
option (maxrecursion 0);

If you want a view, then just add create view as before the with.

Upvotes: 2

Sander
Sander

Reputation: 4042

A recursive common table expression could split those ranges into individual rows.

Sample data

declare @currencies table
(
    curr nvarchar(3),
    factor int,
    yearFrom int,
    yearTo int
);

insert into @currencies (curr, factor, yearFrom, yearTo) values
('EUR', 10, 2000, 2005),
('EUR', 15, 2006, 2008),
('USD', 12, 2001, 2004);

Solution

with cte as
(
    select cur.curr, cur.factor, min(cur.yearFrom) as yearMin, max(cur.yearTo) as yearMax
    from @currencies cur
    group by cur.curr, cur.factor
        union all
    select cte.curr, cte.factor, cte.yearMin+1, cte.yearMax
    from cte
    where cte.yearMin < cte.yearMax
)
select c.curr, c.yearMin as [year], c.factor
from cte c
order by c.curr, c.yearMin;

Result

curr year        factor
---- ----------- -----------
EUR  2000        10
EUR  2001        10
EUR  2002        10
EUR  2003        10
EUR  2004        10
EUR  2005        10
EUR  2006        15
EUR  2007        15
EUR  2008        15
USD  2001        12
USD  2002        12
USD  2003        12
USD  2004        12

Upvotes: 2

Related Questions