Login     Register

        Contact Us     Search

XLeratorDB/math Documentation

SQL Server MINVERSE function


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.
·         Use MINVERSEN for a table in normal form.
·         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

SQL Server Matrix Inverse 
 
 
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

SQL Server Matrix Inverse
 

See Also

·         Blog Post - SQL Server Matrix Functions


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service