asm1212 (1/30/2015)
Luis, or someone else, I am a fellow DBA but I have not seen this before! If it is not too much to ask, can you just kind of explain what this code is doing! I am familiar with CTE's, ROW_NUMBER() OVER, but I guess I am confused as to what the SELECT 1 UNION ALL over and over is really doing...And the WITH E(n), and in the ROW_NUMBER SELECT statement, the FROM E a, E b?
That has me all confused! I know the output is exactly what I need, I just would like to know how the code is working to achieve it...
Thanks
Sure, I'll try to explain.
The SELECT 1 UNION ALL are just generating rows. If you run the code inside the first CTE, you'll get 12 rows with a single column having always 1. The value is not important, we just need the rows.
WITH E(n) is declaring a CTE named E which will have only one column named n.
FROM E a, E b is doing a CROSS JOIN with the CTE to go from 12 rows to 144 (12*12 or 12^2). This is the old join syntax defined by standard SQL-86, while CROSS JOIN is SQL-92 version. E is the name of the CTE and a and b are alias.
Is this clear enough? I'd suggest you to comment every part that you might have trouble remembering so you can immediately remember what it's doing every time you look at the code.