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:
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