MATRIX

Updated 2024-03-28 19:05:40.297000

Syntax

SELECT * FROM [westclintech].[wct].[MATRIX] (
   <@M, nvarchar(max),>)

Description

Use the table-valued function MATRIX to convert the string representation of a matrix into 3rd-normal form.

Arguments

@M

the string representation of the matrix where column values are comma-delimited and rows are semi-colon delimited.

Return Type

table

{"columns": [{"field": "colName", "headerName": "Name", "header": "name"}, {"field": "colDatatype", "headerName": "Type", "header": "type"}, {"field": "colDesc", "headerName": "Description", "header": "description", "minWidth": 1000}], "rows": [{"id": "77a46336-6124-4c0d-8113-303ccaf7b213", "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based row index for the matrix"}, {"id": "18b5ad6a-f97a-437f-b04b-32de6ba16685", "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based column index for the matrix"}, {"id": "e489ecc6-c692-459c-a622-7a2288ee9015", "colName": "ItemValue", "colDatatype": "float", "colDesc": "The value at RowNum, ColNum"}]}

Remarks

If the array contains NULL, then NULL will be returned.

The function returns an error if the array contains a non-numeric value.

Examples

DECLARE @M as nvarchar(max) = '1,2,3,4,5,6,7;8,9,10,11,12,13,14;15,16,17,18,19,20,21;22,23,24,25,26,27,28'

SELECT *
FROM wct.Matrix(@M)

This produces the following result.

{
    "columns": [
        {
            "field": "RowNum",
            "headerClass": "ag-right-aligned-header",
            "cellClass": "ag-right-aligned-cell"
        },
        {
            "field": "ColNum",
            "headerClass": "ag-right-aligned-header",
            "cellClass": "ag-right-aligned-cell"
        },
        {
            "field": "ItemValue",
            "headerClass": "ag-right-aligned-header",
            "cellClass": "ag-right-aligned-cell"
        }
    ],
    "rows": [
        {
            "RowNum": 0,
            "ColNum": 0,
            "ItemValue": 1.000000000000000e+000
        },
        {
            "RowNum": 0,
            "ColNum": 1,
            "ItemValue": 2.000000000000000e+000
        },
        {
            "RowNum": 0,
            "ColNum": 2,
            "ItemValue": 3.000000000000000e+000
        },
        {
            "RowNum": 0,
            "ColNum": 3,
            "ItemValue": 4.000000000000000e+000
        },
        {
            "RowNum": 0,
            "ColNum": 4,
            "ItemValue": 5.000000000000000e+000
        },
        {
            "RowNum": 0,
            "ColNum": 5,
            "ItemValue": 6.000000000000000e+000
        },
        {
            "RowNum": 0,
            "ColNum": 6,
            "ItemValue": 7.000000000000000e+000
        },
        {
            "RowNum": 1,
            "ColNum": 0,
            "ItemValue": 8.000000000000000e+000
        },
        {
            "RowNum": 1,
            "ColNum": 1,
            "ItemValue": 9.000000000000000e+000
        },
        {
            "RowNum": 1,
            "ColNum": 2,
            "ItemValue": 1.000000000000000e+001
        },
        {
            "RowNum": 1,
            "ColNum": 3,
            "ItemValue": 1.100000000000000e+001
        },
        {
            "RowNum": 1,
            "ColNum": 4,
            "ItemValue": 1.200000000000000e+001
        },
        {
            "RowNum": 1,
            "ColNum": 5,
            "ItemValue": 1.300000000000000e+001
        },
        {
            "RowNum": 1,
            "ColNum": 6,
            "ItemValue": 1.400000000000000e+001
        },
        {
            "RowNum": 2,
            "ColNum": 0,
            "ItemValue": 1.500000000000000e+001
        },
        {
            "RowNum": 2,
            "ColNum": 1,
            "ItemValue": 1.600000000000000e+001
        },
        {
            "RowNum": 2,
            "ColNum": 2,
            "ItemValue": 1.700000000000000e+001
        },
        {
            "RowNum": 2,
            "ColNum": 3,
            "ItemValue": 1.800000000000000e+001
        },
        {
            "RowNum": 2,
            "ColNum": 4,
            "ItemValue": 1.900000000000000e+001
        },
        {
            "RowNum": 2,
            "ColNum": 5,
            "ItemValue": 2.000000000000000e+001
        },
        {
            "RowNum": 2,
            "ColNum": 6,
            "ItemValue": 2.100000000000000e+001
        },
        {
            "RowNum": 3,
            "ColNum": 0,
            "ItemValue": 2.200000000000000e+001
        },
        {
            "RowNum": 3,
            "ColNum": 1,
            "ItemValue": 2.300000000000000e+001
        },
        {
            "RowNum": 3,
            "ColNum": 2,
            "ItemValue": 2.400000000000000e+001
        },
        {
            "RowNum": 3,
            "ColNum": 3,
            "ItemValue": 2.500000000000000e+001
        },
        {
            "RowNum": 3,
            "ColNum": 4,
            "ItemValue": 2.600000000000000e+001
        },
        {
            "RowNum": 3,
            "ColNum": 5,
            "ItemValue": 2.700000000000000e+001
        },
        {
            "RowNum": 3,
            "ColNum": 6,
            "ItemValue": 2.800000000000000e+001
        }
    ]
}

In this example we take the reciprocal the @M matrix, calculate the cross-product , calculate its inverse and pivot the resultant table.

SET @M = wct.MUPDATE(1,NULL,NULL,NULL,NULL,'/',@M,NULL,NULL,NULL,NULL)

SELECT [0],[1],[2],[3]
FROM wct.Matrix(wct.MATMULT(@M,wct.TRANSPOSE(@M)))d
PIVOT (MAX(itemvalue) FOR ColNum in ([0],[1],[2],[3]))pvt
ORDER BY RowNum

This produces the following result.

{
    "columns": [
        {
            "field": "0",
            "headerClass": "ag-right-aligned-header",
            "cellClass": "ag-right-aligned-cell",
            "minWidth": 180
        },
        {
            "field": "1",
            "headerClass": "ag-right-aligned-header",
            "cellClass": "ag-right-aligned-cell",
            "minWidth": 180
        },
        {
            "field": "2",
            "headerClass": "ag-right-aligned-header",
            "cellClass": "ag-right-aligned-cell",
            "minWidth": 180
        },
        {
            "field": "3",
            "headerClass": "ag-right-aligned-header",
            "cellClass": "ag-right-aligned-cell",
            "minWidth": 180
        }
    ],
    "rows": [
        {
            "0": 1.511797052154200e+000,
            "1": 2.763074227359940e-001,
            "2": 1.570757689040530e-001,
            "3": 1.100497527930240e-001
        },
        {
            "0": 2.763074227359940e-001,
            "1": 6.419878684634760e-002,
            "2": 3.784411507211150e-002,
            "3": 2.692091782153890e-002
        },
        {
            "0": 1.570757689040530e-001,
            "1": 3.784411507211150e-002,
            "2": 2.243497860862580e-002,
            "3": 1.599772057096640e-002
        },
        {
            "0": 1.100497527930240e-001,
            "1": 2.692091782153890e-002,
            "2": 1.599772057096640e-002,
            "3": 1.141912823922530e-002
        }
    ]
}