I was looking through some programming exercises and one that came up was build a multiplication table. This is really easy in most programming languages. You set up two loops, roll through them and you produce something that looks like
1 2 3
2 4 6
3 6 9
The easiest way to do it in SQL would be to use two WHILE loops and roll through it, but that wouldn’t be much fun so I’m going to do it differently. I’m going to make the easy, hard.
STEP 1: Create and populate a temporary Table
CREATE TABLE #Temp
(
col1 INT,
col2 INT
)
DECLARE @ctr INT = 1
WHILE @ctr <= 12
BEGIN
INSERT INTO #Temp VALUES (@ctr, @ctr)
SET @ctr += 1
END
Already, a bad sign, I have two columns. Why two columns? Because it makes the PIVOT table that I’l create in a second work.
Step 2: Create a pivot table on the data
SELECT * FROM #Temp
PIVOT (SUM(col1) FOR col2 IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS a
This produces a result set that looks like the following:
1 2 3 4 5 6 7 8 9 10 11 12
1 2 3 4 5 6 7 8 9 10 11 12
Step 3: Repeat this data 12 times
Before I repeat the data I need to do a quick math class. It’ll make sense in a second. Once you’ve learned the multiplication tables we just automatically think 12 * 6 = 72. In other words, we know it and we forget that 12 * 6 is actually 12 + 12 + 12 + 12 + 12 + 12. Why does this matter here? Because we’re working with a result set and we’re going to build a running total in a second.
So lets replicate the data with a FULL OUTER JOIN. In case you forget a FULL OUTER JOIN returns all combinations from both sides of the table. Since one side of our join is a single row this is that rare time that a FULL OUTER JOIN is going to be useful.
SELECT *
FROM #Temp
FULL OUTER JOIN
(
SELECT * FROM #Temp
PIVOT (SUM(col1) FOR col2 IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS a
) AS b ON [12] >= col1
ORDER BY col1
Which Produces
col1 col2 1 2 3 4 5 6 7 8 9 10 11 12
1 1 1 2 3 4 5 6 7 8 9 10 11 12
2 2 1 2 3 4 5 6 7 8 9 10 11 12
3 3 1 2 3 4 5 6 7 8 9 10 11 12
4 4 1 2 3 4 5 6 7 8 9 10 11 12
5 5 1 2 3 4 5 6 7 8 9 10 11 12
6 6 1 2 3 4 5 6 7 8 9 10 11 12
7 7 1 2 3 4 5 6 7 8 9 10 11 12
8 8 1 2 3 4 5 6 7 8 9 10 11 12
9 9 1 2 3 4 5 6 7 8 9 10 11 12
10 10 1 2 3 4 5 6 7 8 9 10 11 12
11 11 1 2 3 4 5 6 7 8 9 10 11 12
12 12 1 2 3 4 5 6 7 8 9 10 11 12
Now you are probably wondering about some of the code. First, ON [12] >= col1 is there because I have to have a JOIN condition, I want the join to always fire and this is how I did it. Almost any test would work that didn’t exclude specific values in the data. So, 1=1, 1 <> 1, [12] IS NOT NULL, etc, would all be fine. On the other hand, [12] IS NULL or [12] < 6 wouldn’t work.
Second, the ORDER BY clause is there because I need the sort order guaranteed.
Now lets clean this up a bit.
STEP 4: Create running totals in the data
SELECT col1 AS ‘ ‘,
SUM([1]) OVER ( ORDER BY col1) AS ‘1’,
SUM([2]) OVER ( ORDER BY col1) AS ‘2’,
SUM([3]) OVER ( ORDER BY col1) AS ‘3’,
SUM([4]) OVER ( ORDER BY col1) AS ‘4’,
SUM([5]) OVER ( ORDER BY col1) AS ‘5’,
SUM([6]) OVER ( ORDER BY col1) AS ‘6’,
SUM([7]) OVER ( ORDER BY col1) AS ‘7’,
SUM([8]) OVER ( ORDER BY col1) AS ‘8’,
SUM([9]) OVER ( ORDER BY col1) AS ‘9’,
SUM([10]) OVER ( ORDER BY col1) AS ’10’,
SUM([11]) OVER ( ORDER BY col1) AS ’11’,
SUM([12]) OVER ( ORDER BY col1) AS ’12’
FROM #Temp
FULL OUTER JOIN
(
SELECT * FROM #Temp
PIVOT (SUM(col1) FOR col2 IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS a
) AS b ON [12] >= col1
ORDER BY col1
Which produces
1 2 3 4 5 6 7 8 9 10 11 12
1 1 2 3 4 5 6 7 8 9 10 11 12
2 2 4 6 8 10 12 14 16 18 20 22 24
3 3 6 9 12 15 18 21 24 27 30 33 36
4 4 8 12 16 20 24 28 32 36 40 44 48
5 5 10 15 20 25 30 35 40 45 50 55 60
6 6 12 18 24 30 36 42 48 54 60 66 72
7 7 14 21 28 35 42 49 56 63 70 77 84
8 8 16 24 32 40 48 56 64 72 80 88 96
9 9 18 27 36 45 54 63 72 81 90 99 108
10 10 20 30 40 50 60 70 80 90 100 110 120
11 11 22 33 44 55 66 77 88 99 110 121 132
12 12 24 36 48 60 72 84 96 108 120 132 144
These changes do a couple of things. The obvious one is that it uses SUM with an OVER clause to generate running totals for each column. Remember how I said 12 * 6 is actually 12 + 12 + 12 + 12 + 12 + 12? Well, this is what it was leading up to. I also dropped col2 from the results because I wanted to have a cleaner axis and aliased col1 as ‘ ‘ so the results looked a bit cleaner.
STEP 5: Drop it all in a stored procedure
IF OBJECT_ID(‘sp_junk’,’P’) IS NOT NULL
DROP PROCEDURE sp_junk;
GO
CREATE PROCEDURE sp_junk
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #Temp
(
col1 INT,
col2 INT
)
DECLARE @ctr INT = 1
WHILE @ctr <= 12
BEGIN
INSERT INTO #Temp VALUES (@ctr, @ctr)
SET @ctr += 1
END
SELECT col1 AS ‘ ‘,
SUM([1]) OVER ( ORDER BY col1) AS ‘1’,
SUM([2]) OVER ( ORDER BY col1) AS ‘2’,
SUM([3]) OVER ( ORDER BY col1) AS ‘3’,
SUM([4]) OVER ( ORDER BY col1) AS ‘4’,
SUM([5]) OVER ( ORDER BY col1) AS ‘5’,
SUM([6]) OVER ( ORDER BY col1) AS ‘6’,
SUM([7]) OVER ( ORDER BY col1) AS ‘7’,
SUM([8]) OVER ( ORDER BY col1) AS ‘8’,
SUM([9]) OVER ( ORDER BY col1) AS ‘9’,
SUM([10]) OVER ( ORDER BY col1) AS ’10’,
SUM([11]) OVER ( ORDER BY col1) AS ’11’,
SUM([12]) OVER ( ORDER BY col1) AS ’12’
FROM #Temp
FULL OUTER JOIN
(
SELECT * FROM #Temp
PIVOT (SUM(col1) FOR col2 IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS a
) AS b ON [12] >= col1
ORDER BY col1
SET NOCOUNT OFF
END
Other than the stored procedure I made two other minor changes. Temporary tables are automatically dropped when you exit a stored procedure so I removed the DROP TABLE code and I also added the SET NOCOUNT option to get rid of the INSERT messages.
STEP 6: Wonder why I did it
Honestly, and I ‘ll talk about this someday, I see SQL as at least something of an art. Yes, there are ways to optimize code, to be precise, and you want to do those kinds of things, especially if you are being paid, but sometimes, “I did it because it was there” is a good enough answer.