Alec. Alec. - 3 months ago 5
SQL Question

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:


Thanks for your help


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