How can I split this table to:

To show as:

CodePudding user response：

You could also use recursion:

With MyTable as (
select YearRange='2014-2018', Make='Nissan', Model='GT-R' union all
select YearRange='2015-2020', Make='Ford', Model='Kuga'
),
AllYears as (
select ThisYear=cast(left(YearRange,4) as integer), LastYear=cast(right(YearRange,4) as integer), Make, Model from MyTable
union all
select ThisYear=ThisYear 1, LastYear=LastYear, Make, Model from AllYears where ThisYear < LastYear
)
select ThisYear, Make, Model from AllYears
order by Make, Model, ThisYear

CodePudding user response：

Just another option using an ad-hoc tally/numbers table in concert with a CROSS APPLY

Example

Select [Year] = R1 N
      ,[Vehicle Make]
      ,[Vehicle Model]
 From  YourTable A
 Cross Apply ( values ( convert(int,left([Year Range] ,4))
                       ,convert(int,right([Year Range],4)) 
                      ) 
             )B(R1,R2)
 Join (Select Top 100 N=-1 Row_Number() Over (Order By (Select NULL)) 
         From  master..spt_values n1 ) C on N<=R2-R1

Results

Year    Vehicle Make    Vehicle Model
2014    Nissan          GT-R
2015    Nissan          GT-R
2016    Nissan          GT-R
2017    Nissan          GT-R
2018    Nissan          GT-R

CodePudding user response：

You can achieve this using recursive CTE to generate list of years and then joining the same with the table using BETWEEN clause.

DECLARE @table table(YearRange varchar(20), VehicleMake varchar(20),    VehicleModel varchar(20))

insert into @table values
('2014-2018','Nissan','GT-R');

;WITH Cte_year as
(
SELECT 2014 as y
UNION ALL
SELECT Y 1 as Y
from Cte_year
where y < 2017
), cte_rangesplit as
(
SELECT left(yearrange,4) as startRange, right(yearrange,4) as endRange
,VehicleMake, VehicleModel
from @table) 
SELECT cy.y, c.VehicleMake, c.VehicleModel FROM Cte_year as cy
inner join cte_rangesplit as c
on cy.y between c.startRange and c.endRange