Alec. Alec. - 1 year ago 104
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

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download