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
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.
See Also