Here's a rundown of the various methods to traverse a hierarchy. You might want to try a couple of them to see what performs best for your specific situation.

https://www.simple-talk.com/sql/performance/the-performance-of-traversing-a-sql-hierarchy-/

This is similar to Itzik Ben-Gan's Depleting Quantities challenge:

http://sqlmag.com/t-sql/t-sql-challenges-replenishing-and-depleting-quantities

It can be solved with a QU as Alan.B has suggested.

However I should warn you that there are those that are not...

Perhaps you can't design it but maybe you can redesign it.

DECLARE @MyDims TABLE

(

Dimensions VARCHAR(100)

,[Length] AS(LEFT(Dimensions, CHARINDEX('X', Dimensions)-1))

...

Perhaps this?

SELECT CONVERT(VARCHAR(100), CAST('2014-01-01' AS DATETIMEOFFSET), 126)

Help us help you next time by providing consumable sample data as I have done.

WITH SampleData (EmailID, Email, [Status]) AS(

SELECT 6207,'abbdie@nofavtt.org',2

UNION ALL...

There are many ways to skin a hierarchical cat:

https://www.simple-talk.com/sql/performance/the-performance-of-traversing-a-sql-hierarchy-/

Perhaps this is what you mean.

WITH SampleData (date, HourType, hours, HourValue, status) AS(

SELECT 'day1','m',1,1,'y'

UNION ALL SELECT 'day1','m',2,1,'y'

UNION...

This looks quite a bit like a CSV export I recently did that was to be imported into AccPac.

Is what you want really a CSV file format?

That can be done....

Or if you want all months within the range (not just the ones where the counts change), you can do this:

WITH SampleData (AccountNo, OpenDate, CloseDate) AS(

...

Another option:

SELECT CONVERT(VARCHAR(6), d, 112)

,CONVERT(VARCHAR(7), d, 20) + '-01'

FROM (SELECT DATEADD(month, -1, GETDATE())) a (d);

There is also the following possibility using Jeff Moden's community-improved, delimited string splitter and a quirky update.

DECLARE @Parameter VARCHAR(8000) = 'this is a very long string just being used to...

Or this?

WITH YourTable (ID,Client,TxDate,GrossCost) AS

(

SELECT 1,'abc',CAST('11/10/2014' AS DATE),$10.07

UNION ALL SELECT 2,'Dest','11/10/2014',$10.07

UNION ALL SELECT 3,'Dest','11/10/2014',$10.07

UNION...

Why not CROSS APPLY VALUES instead?

WITH YourTable (PeriodType, PeriodValue, AgentNo, Metric1, Metric2, Metric3) AS

(

SELECT 'Year','2014','EY57627','1833','609.87','0.804878' UNION ALL

SELECT 'Year','2014','EY37480','11784','401.413','0.833333' UNION ALL

SELECT 'Year','2014','EY37715','0','0','0' UNION ALL

SELECT 'Year','2014','6069748','8242','479.842','0.857542' UNION ALL

SELECT 'Year','2014','ex51539','0','0','0' UNION ALL

SELECT 'Year','2014','6071493','4833','594.724','0.7866'...

Perhaps this?

WITH Combos (Distance, Gender) AS

(

SELECT '500','Man'

UNION ALL SELECT '1000','Man'

UNION ALL SELECT '1500','Man'

UNION ALL...

How about if you just stuff it?

WITH LegacyData (LegacyDate) as

(

SELECT '0980412' UNION ALL

SELECT '1100323' UNION ALL

SELECT '940930' UNION ALL

SELECT '941027' UNION ALL

SELECT...