MINVERSE
Updated: 17 August 2010
Use MINVERSE to calculate the matrix inverse of a square (N x N) array.
For matrices in normalized form, use the MINVERSEN function.
Syntax
SELECT * FROM [wctMath].[wct].[MINVERSE] (
<@Matrix_TableName, nvarchar(4000),>
,<@Matrix_ColumnNames, nvarchar(4000),>
,<@Matrix_GroupedColumnName, nvarchar(4000),>
,<@Matrix_GroupedColumnValue, sql_variant,>)
Arguments
@Matrix_ TableName
the name, as text, of the table or view that contains the values in the square (N x N) array to be used in the MINVERSE calculation.
@Matrix_ ColumnNames
the name, as text, of the columns in the table or view specified by @Matrix_TableName that contains the array values to be used in the MMULT calculation. Data returned from the @Matrix_ColumnNames must be of the type float or of a type that implicitly converts to float.
@Matrix_GroupedColumnName
the name, as text, of the column in the table or view specified by @Matrix_TableName which will be used for grouping the results.
@Matrix_GroupedColumnValue
the column value to do the grouping on.
Return Types
TABLE (
[RowNum] [int] NULL,
[ColNum] [int] NULL,
[ItemValue] [float] NULL
Remarks
· The number of columns in the array must equal the number of rows in the array.
· If the matrix determinant is equal to zero an error will be returned.
· Use the MINVERSE_q function for more complex queries or to pass the matrices directly into the function without having to store them in a table.
· If the array contains NULL, then NULL will be returned.
· The function returns an error if the array contains a non-numeric value.
Examples
CREATE TABLE #i (
MatrixID nvarchar(5),
rowno int,
Col00 float,
Col01 float,
Col02 float,
Col03 float,
Col04 float,
Col05 float,
Col06 float,
Col07 float,
Col08 float,
Col09 float
)
INSERT INTO #i (Matrixid, rowno, Col00, col01, col02) VALUES ('1A',0,1,2,1)
INSERT INTO #i (Matrixid, rowno, Col00, col01, col02) VALUES ('1A',1,3,4,-1)
INSERT INTO #i (Matrixid, rowno, Col00, col01, col02) VALUES ('1A',2,0,2,0)
SELECT *
FROM wct.MINVERSE(
'#i'
,'col00, col01,col02'
,'MatrixID'
,'1A'
)
This produces the following result
If we wanted to return the results in matrix form, we can use the PIVOT function, though this requires knowing the number of columns returned by the function (which is equal to the number of rows).
SELECT [0],[1],[2]
FROM (
SELECT C.*
FROM wct.MINVERSE(
'#i'
,'col00, col01,col02'
,'MatrixID'
,'1A'
) C
) M PIVOT (
MAX(ItemValue)
FOR colnum IN ([0],[1],[2])
) AS pvt
ORDER BY rownum
This produces the following result
See Also