CODEWITHSUNDEEP
sqlsql-server
Alec.
Alec.

Reputation: 5525

How to generate combinations

I have a requirement to create a table with an identifier column. The identifier data will be comprised of 3 parts, the first being a letter [A-Z], the second being a number [1-42] and the third being again a number [1-6].

I was wondering the quickest and best way to go about this as I'm really stuck. The output should look like this:

A-1-1
A-1-2
A-1-3
...
Z-42-6

Thanks for your help

Upvotes: 3

Views: 243

Answers (5)

gofr1
gofr1

Reputation: 15977

One more way:

;WITH cte AS (
SELECT  1 as digit
UNION ALL
SELECT  digit + 1
FROM cte
WHERE digit < 90
)

SELECT  CHAR(c1.digit) + '-' +
        CAST(c2.digit as nvarchar(2)) + '-' +
        CAST(c3.digit as nvarchar(2)) as seq
FROM cte c1
CROSS JOIN (SELECT digit FROM cte WHERE digit between 1 and 42) c2
CROSS JOIN (SELECT digit FROM cte WHERE digit between 1 and 6) c3
WHERE c1.digit between 65 and 90 --65..90 in ASCII is A..Z

Output:

seq
A-1-1
A-1-2
A-1-3
A-1-4
A-1-5
A-1-6
A-2-1
A-2-2
A-2-3
A-2-4
A-2-5
A-2-6
...
Z-42-3
Z-42-4
Z-42-5
Z-42-6

Upvotes: 1

Me.Name
Me.Name

Reputation: 12534

Just for fun, a mathematical approach:

with cte as
(
select 0 nr
union all
select nr+1 from cte where nr < 6551 --(26 * 42 * 6 = 6552 , 0 based = 6551)
)
select char(65 + (nr / 252)), 1 + ((nr / 6) % 42),  1 + nr % 6, * from cte -- Letter: divider = 6 * 42 = 252   ,  65 = 'A'
option (maxrecursion 10000)

The cte only generated a stream of numbers from 0 to 6551 (could be done with other approaches as well). After that each segment of the sequence can be calculated. But for the record, once a sequence is created, I like Zohar's solution best :)

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82474

A Tally table would save you the need to write all the values one by one.
If you don't already have a tally table, read this post on the best way to create one.

SELECT Letter +'-'+ cast(fn as varchar(2)) +'-'+ cast(sn as char(1))
FROM (SELECT CHAR(Number) As Letter FROM Tally  WHERE Number BETWEEN 65 AND 90) a
CROSS JOIN (SELECT Number as fn FROM Tally WHERE Number BETWEEN 1 AND 42) b
CROSS JOIN (SELECT Number as sn FROM Tally WHERE Number BETWEEN 1 AND 6) c

Upvotes: 2

Jamiec
Jamiec

Reputation: 136074

Here is a cut-down version using CROSS JOIN acrross 3 valued tables

SELECT v1.val + '-' + CAST(v2.val AS VARCHAR(5)) + '-' + cast(v3.val AS VARCHAR(5)) 
FROM 
(VALUES ('A'),('B'),('C'),('D')) v1(val)
CROSS JOIN
(VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16)) v2(val)
CROSS JOIN
(VALUES (1),(2),(3),(4),(5),(6)) v3(val)

Upvotes: 2

sagi
sagi

Reputation: 40471

You should use CROSS JOIN with derived tables containing all letters/numbers needed

SELECT letters.let + '-' + numbers.num + '-' + numbers2.num
FROM(SELECT 'A' as let UNION ALL SELECT 'B' .....) letters
CROSS JOIN(SELECT '1' as num UNION ALL SELECT '2' ....) numbers -- up to 42
CROSS JOIN(SELECT '1' as num UNION ALL SELECT '2' ....) numbers2 -- up to 6

Upvotes: 2

Related Questions