如何在SQL中将年份范围拆分为年份行

Question

如何将此表拆分为：

车型

年份范围		车辆品牌
2014-2018	日产	GT-R

只是另一个选项，使用与CROSS APPLY 协同使用的特殊计数/数字表

示例

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

结果

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

您也可以使用递归：

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

您可以使用递归CTE来生成年份列表，然后使用BETWEEN子句将其与表连接。

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

>

y	车辆模型
2014	日产	GT-R
2015	日产	GT-R
2016		日产	GT-R
2017	日产	GT-R