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

on
118 Views
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

## View Solution Only

Senior 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
``````
CFO

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)
CFO

Commented:
Perfect thank you!
Unlock the solution to this question.