Repeat the rows based on the Range of two columns

Question

I have created a fiddle here for the below data and for the query I have tried.

I have table like below.

My expected output is as below

Logic:

I have to create the report as shown above. The logic is to repeat the same rows from the starting number to ending material number. Find the difference and if there is a difference repeat the rows for each material number.All other column value remains same.

Data format.

MaterialNo will have only one hyphen.(varchar type)
The number of characters before and after the hyphen may vary.
The difference may go upto 150.

So what i have tried

WITH cte
AS (
    SELECT Materialno_start,Materialno_end,name,mtype,noofstock
        ,starts.st AS ns,ends.ed AS nd,diff.s AS d,i = 1
        ,n = convert(VARCHAR(30), starts.st)
        ,n.base AS bs
    FROM data
    CROSS APPLY (VALUES (len(Materialno_start))) leng(mn)
    CROSS APPLY (VALUES (charindex('-', Materialno_start)) ) s(hyp)
    CROSS APPLY (VALUES (substring(Materialno_start, s.hyp - leng.mn + 1, leng.mn))) n(base)
    CROSS APPLY (VALUES (substring(Materialno_start, s.hyp + 1, leng.mn)) ) starts(st)
    CROSS APPLY (VALUES (substring(coalesce(Materialno_end, Materialno_start), s.hyp + 1, leng.mn)) ) ends(ed)
    CROSS APPLY (VALUES (convert(INT, ends.ed) - convert(INT, starts.st))) diff(s)

    UNION ALL

    SELECT Materialno_start,Materialno_end,name,mtype,noofstock ,ns,nd,d,i = i + 1
        ,n = convert(VARCHAR(30), n + 1)
        ,bs
    FROM cte
    WHERE i <= d
    )
SELECT Materialno_start
    ,Materialno_end
    ,bs + n AS MaterialNo
    ,Name
    ,mtype
    ,noofstock
FROM cte
ORDER BY 1

It is giving me the required output. But i'm not sure if it is efficient as there are so many CROSS APPLY and my production data may have around 70k rows and it may go upto 120k rows after splitting. I would like to know if this can be done in any other way more efficiently or what are the things I can improve in this query. I don't have access to production data or QA.So i cannot test this in real data. I was given sample data of 100 rows and I used this query to achieve my output.

user_2029983 · Answer

In simple terms, a Tally looks like this:

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (200)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3)
SELECT *
FROM Tally;

Then I suspect what you want to do, to replace the rCTe (and some of the Cross applies), would be something like this:

WITH N AS
    (SELECT N
     FROM (VALUES (NULL),
                  (NULL),
                  (NULL),
                  (NULL),
                  (NULL),
                  (NULL),
                  (NULL),
                  (NULL),
                  (NULL),
                  (NULL)) N (N) ),
Tally AS
    (SELECT TOP (200)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
     FROM N N1,
          N N2,
          N N3),
cte AS
    (SELECT D.Materialno_start,
            D.Materialno_end,
            D.Name,
            D.MType,
            D.Noofstock,
            CONCAT(LEFT(D.Materialno_start,CHARINDEX('-',D.Materialno_start)),V.NoStart + ISNULL(T.I,0)) AS NewID
     FROM dbo.[data] D
          CROSS APPLY (VALUES(TRY_CONVERT(int,STUFF(D.Materialno_start,1,CHARINDEX('-',D.Materialno_start),'')),TRY_CONVERT(int,STUFF(D.Materialno_end,1,CHARINDEX('-',D.Materialno_end),'')))) V(NoStart,NoEnd)       
          LEFT JOIN Tally T ON T.I <= V.NoEnd - V.NoStart)
SELECT Materialno_start,
       Materialno_end,
       Materialno_start AS MaterialNo,
       Name,
       mtype,
       noofstock,
       NewID
FROM cte
ORDER BY cte.Materialno_start;

Repeat the rows based on the Range of two columns

1 Answers