Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server MMULT function


MMULTN_q
 
Updated: 17 August 2010

Use MMULTN_q to calculate the matrix product of two arrays. The result is a TABLE where the maximum row number is equal to the number of rows in the first array and the maximum column number is the number of columns in the second array
 
For matrices not in normalized form, use the MMULT_q function.
Syntax
SELECT * FROM [wctMath].[wct].[MMULT_q] (
  <@Matrix_A_RangeQuery, nvarchar(max),>
 ,<@Matrix_B_RangeQuery, nvarchar(max),>)
Arguments
 
@Matrix_A_RangeQuery
the select statement, as text, used to determine the ‘A’ matrix to be used in this function. The resultant table from the SELECT statement must contain three columns: the row number, the column number, and the item value. The item value returned from the @Matrix_A_RangeQuery SELECT must be of the type float or of a type that implicitly converts to float.
@Matrix_B_RangeQuery
the select statement, as text, used to determine the ‘B’ matrix to be used in this function. The resultant table from the SELECT statement must contain three columns: the row number, the column number, and the item value. The item value returned from the @Matrix_B_RangeQuery SELECT must be of the type float or of a type that implicitly converts to float.
Return Types
TABLE (
      [RowNum] [int] NULL,
      [ColNum] [int] NULL,
      [ItemValue] [float] NULL
Remarks
·         The number of columns in the ‘A’ array must be equal to the number of rows in the ‘B’ array or an error will be returned.
·         Use the MMULTN function for simpler queries.
·         Use MMULT_q for a table not in normal form.
·         If the array contains NULL, then NULL will be returned.
·         If the array contains a blank, it will be treated as zero.
·         The function returns an error if either array contains a non-numeric value.
Examples
In this example, we calculate the matrix product directly from the SELECT statement.
SELECT *
FROM wct.MMULTN_q(
      'SELECT 0,0,5 UNION ALL
      SELECT 0,1,6 UNION ALL
      SELECT 0,2,7 UNION ALL
      SELECT 0,3,8 UNION ALL
      SELECT 1,0,9 UNION ALL
      SELECT 1,1,10 UNION ALL
      SELECT 1,2,-11 UNION ALL
      SELECT 1,3,12 UNION ALL
      SELECT 2,0,16 UNION ALL
      SELECT 2,1,15 UNION ALL
      SELECT 2,2,14 UNION ALL
      SELECT 2,3,13',
      'SELECT 0,0,1 UNION ALL
      SELECT 0,1,8 UNION ALL
      SELECT 0,2,9 UNION ALL
      SELECT 0,3,13 UNION ALL
      SELECT 1,0,2 UNION ALL
      SELECT 1,1,7 UNION ALL
      SELECT 1,2,10 UNION ALL
      SELECT 1,3,14 UNION ALL
      SELECT 2,0,-3 UNION ALL
      SELECT 2,1,6 UNION ALL
      SELECT 2,2,11 UNION ALL
      SELECT 2,3,15 UNION ALL
      SELECT 3,0,4 UNION ALL
      SELECT 3,1,5 UNION ALL
      SELECT 3,2,12 UNION ALL
      SELECT 3,3,16'
      ) C

This produces the following result

SQL Server Matrix Multiplication

If we wanted to SELECT the matrix product from a table, then the SELECT statement would look like this.


CREATE TABLE #m (
      MatrixID    varchar(5),
      rowno       int,
      colno       int,
      val               float
)
 
INSERT INTO #M VALUES('2A',0,0,2)
INSERT INTO #M VALUES('2A',0,1,4)
INSERT INTO #M VALUES('2A',1,0,8)
INSERT INTO #M VALUES('2A',1,1,6)
INSERT INTO #M VALUES('2B',0,0,1)
INSERT INTO #M VALUES('2B',0,1,3)
INSERT INTO #M VALUES('2B',1,0,7)
INSERT INTO #M VALUES('2B',1,1,5)
 
SELECT *
FROM wct.MMULTN_q(
      'SELECT rowno
      ,colno
      ,val
      FROM #m
      WHERE MatrixID = ''2A''',
      'SELECT rowno
      ,colno
      ,val
      FROM #m
      WHERE MatrixID = ''2B'''
      )

This produces the following result.

SQL Server Matrix Multiplication
 

See Also

·         Blog Post - SQL Server Matrix Functions


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service