RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


SQL Techniques for Performing Operations on Matrices : Page 4

SQL does not support direct operations on matrices, but it does allow easy manipulations with matrices. Learn a few SQL techniques for performing some basic operations on matrices.

Matrices Addition/Subtraction Implementation in SQL
To demonstrate the implementation of matrices addition and subtraction in SQL, create tables A and B, which will represent two sample matrices A5x5 and B5x5 (see Listing 1).

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, A7x8 and B8x9 (see Listing 3).

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

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.

Alex Kozak is a senior DBA/analyst working for SAP Canada. He has more than 15 years of database and programming experience. Microsoft has included some of his articles in the MSDN Library.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date