SQL Techniques for Performing Operations on Matrices : Page 4

Since matrices A and B are the same size, you can add and subtract them. The implementation in SQL is very straightforward (see Listing 2):

Matrices Multiplication Implementation in SQL
Since matrix scalar multiplication in SQL is obvious—simply multiply all columns in the table by a constant—it won't be discussed here. Instead, let's explore a more complicated operation: matrices multiplication. The example in this section will implement matrices multiplication in three steps:

1. Transform matrices A and B into two-dimensional arrays.
2. Calculate the product.
3. Transform calculated product back into matrix form.

Before you start, rebuild tables A and B. This time, they will represent two sample matrices, A_7x8 and B_8x9 (see Listing 3).

Matrices A_7x8 and B_8x9 are compatible because the number of columns in matrix A_7x8 is equal to the number of rows in matrix B_8x9. The result of multiplication is that matrix-product C_7x9 will have seven rows and nine columns: A_7x8 ∙ B_8x9 = C_7x9.

The code in the Listing 4 demonstrates how to perform matrices multiplication. All three steps described earlier in this section are implemented in the one query in Listing 4.

The first and second CTE queries transform matrices A and B into two-dimensional arrays. The third CTE query calculates matrices product according to the formula in Figure 2. The last SQL statement transforms a two-dimensional array into the matrix result.

Using the same approach with two-dimensional arrays, you can easily perform addition and subtraction.

Matrix Transpose Implementation in SQL
To transpose matrix A, just transform that matrix into a two-dimensional array and then transform it back into the matrix form. For reverse transformation, however, you replace the rows with the columns and the columns with the rows (see Listing 5).

Now you know a few SQL techniques for performing addition, subtraction, multiplication, and transpose on matrices.