loop in SQL server program with Table

Question

My expected result is quit difficult to explain so here i have shown sample data.

SourceTable: (I have alphabets in HeadNo column)

HeadNo   |   Start   |   End
---------+-----------+----------
   AA    |   AA0000  | AA9999
   AB    |   AB0000  | AB9999
   AC    |   AC0000  | AC9999
   AD    |   AD0000  | AD9999
    --------------------
    --------------------
    ------- so on ------
   ZZ    |   ZZ0000  | ZZ9999

From this source table, I want to create kind of loop result, where each HeadNo will give return 10000 result for each, starts from 0000 to 9999.

Result should look like:

HeadNo   |   Actual Code
---------+---------------
   AA    |   AA0000
   AA    |   AB0001
   AA    |   AC0002
   AA    |   AD0003
    --------------------
    --------------------
    ------- so on ------
   AA    |   AA9998
   AA    |   AA9999

like wise for each HeadNo

   ZZ    |   ZZ0000
   ZZ    |   ZZ0001
   ZZ    |   ZZ0002
   ZZ    |   ZZ0003
    --------------------
    --------------------
    ------- so on ------
   ZZ    |   ZZ9999

I want to merge and insert into one separate single table.

Will the value of Actual Code always be in the format AA0000? — Thom A
– Thom A ♦, Commented Feb 19, 2019 at 9:39
@Larnu: yes, format will be always with 2 head alphabets and 4 digits.. like AA0000 to ZZ9999 — Irfan
– Irfan, Commented Feb 20, 2019 at 0:30

Thom A · Accepted Answer · 2019-02-19 09:47:04Z

IF every row requires the values 0-9999 then you simply need to CROSS JOIN to a tally table:

WITH N AS(
    SELECT *
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1
         CROSS JOIN N N2 --100
         CROSS JOIN N N3 --1000
         CROSS JOIN N N4 --10000
    )
SELECT YT.HeadNo,
       YT.HeadNo + RIGHT('0000' + CONVERT(varchar(4),T.I),4) AS ActualCode
FROM YourTable YT
     CROSS JOIN Tally T;

If, however, you have actual start and end ranges per HeadNo (like the example below), you'll need to use a little more logic in the JOIN:

WITH VTE AS (
    SELECT *
    FROM (VALUES('AA','AA0000','AA9999'),
                ('AB','AB0000','AB5000'), --Guesssing this is more realistic
                ('AC','AC1000','AC8000'), 
                ('AD','AD0000','AD0100'),
                ('ZZ','ZZ0000','ZZ9999')) V(HeadNo, HeadStart, HeadEnd)),
N AS(
    SELECT *
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1
         CROSS JOIN N N2 --100
         CROSS JOIN N N3 --1000
         CROSS JOIN N N4 --10000
    )
SELECT V.HeadNo,
       V.HeadNo + RIGHT('0000' + CONVERT(varchar(4),T.I),4) AS ActualCode
FROM VTE V
     JOIN Tally T ON T.I BETWEEN STUFF(V.HeadStart,1,2,'') AND STUFF(V.HeadEnd,1,2,'')
ORDER BY V.HeadNo,
         ActualCode;

The second examples assumes that HeadNo will always have the format AA0000; if it doesn't then we're missing important information that should be included in your question.

Thanks a lot, your above query worked as per my requirement.

Michał Turczyn · Accepted Answer · 2019-02-19 10:02:24Z

0

Try below code. I used recursive CTE to obtain numebrs from 0 to 9999 and then cross joined to your HeadNo column:

;with cte as (
  select 0 n
  union all 
  select n + 1 from cte
  where n < 9999
)

select HeadNo, HeadNo + right('0000' + cast(n as varchar(4)), 4) from MyTable
cross join cte option (maxrecursion 0)

edited Feb 19, 2019 at 10:02

answered Feb 19, 2019 at 9:50

Michał Turczyn

41.3k18 gold badges58 silver badges87 bronze badges

2 Comments

Thom A Over a year ago

This won't give the expected results. The OP wants strings of the format 'AA0000' The int value 0 cast to a varchar(4) is the value '0', giving the final result 'AA0'. Also, A CTE like this is a hidden form of RBAR; a tally table would avoid the require for OPTION (MAXRECURSION 0) and likely provide a performance benefit.

Mano Over a year ago

@Michał Turczyn use Order by to get the expected result.

Collectives™ on Stack Overflow

loop in SQL server program with Table

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related