We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

multiply rows by tally table with zero padded concatenation

cskehan
cskehan asked
on
118 Views
Last Modified: 2017-03-25
I am using sql server 2014

How would I edit the following code spinet so that I get 1000 sequential rows instead of 100.

CREATE TABLE Table1
    ([Folder] int, [casefile] varchar(20))
;
   
INSERT INTO Table1
    ([Folder], [casefile])
VALUES
    (1, '002774'),
    (2, '084936'),
    (3, '062810'),
    (4, '002793'),
    (5, '243840')
;

;WITH
Digits AS (
          SELECT 0 AS digit UNION ALL
          SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL  
          SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
          SELECT 9
          )
, Tally AS (
          SELECT [tens].digit * 10 + [ones].digit AS number
          FROM Digits [ones]
          CROSS JOIN Digits [tens]
          )
select
       t.folder
     , right('000000' + convert(varchar(6), (try_cast(t.casefile as int) + tally.number + 1)  ),6) as new_casefile
from table1 t
cross join tally
order by t.folder, tally.number
Comment
Watch Question

Nakul VachhrajaniSenior Manager
CERTIFIED EXPERT

Commented:
I don't have a SSMS in front of me to test it in, but performing another cross join should do the trick.

Basically, you have 10 rows in the "Digits" CTE and are then doing a Cartesian product on itself. So, 10 * 10 = 100. Adding another cross join would be 100 * 10 = 1000 rows.

CREATE TABLE Table1
    ([Folder] int, [casefile] varchar(20))
;
   
INSERT INTO Table1
    ([Folder], [casefile])
VALUES
    (1, '002774'),
    (2, '084936'),
    (3, '062810'),
    (4, '002793'),
    (5, '243840')
;

;WITH
Digits AS (
          SELECT 0 AS digit UNION ALL
          SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL  
          SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
          SELECT 9
          )
, Tally AS (
          SELECT [tens].digit * 10 + [ones].digit AS number
          FROM Digits [ones]
          CROSS JOIN Digits [tens]
          CROSS JOIN Digits [hundreds]
          )
select
       t.folder
     , right('000000' + convert(varchar(6), (try_cast(t.casefile as int) + tally.number + 1)  ),6) as new_casefile
from table1 t
cross join tally
order by t.folder, tally.number 

Open in new window

cskehanCFO

Author

Commented:
This is a sample of what I got adding "cross join digits [hundreds]".

New_Casefile should be unique not repeated.

        folder      new_casefile
1      1      002775
2      1      002775
3      1      002775
4      1      002775
5      1      002775
6      1      002775
7      1      002775
8      1      002775
9      1      002775
10      1      002775
11      1      002776
12      1      002776
13      1      002776
14      1      002776
15      1      002776
16      1      002776
17      1      002776
18      1      002776
19      1      002776
20      1      002776
21      1      002777
22      1      002777
23      1      002777
24      1      002777
25      1      002777
26      1      002777
27      1      002777
28      1      002777
29      1      002777
Senior Manager
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
cskehanCFO

Author

Commented:
Perfect thank you!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.