MMULT

Updated 2023-10-17 13:10:51.337000

Syntax

SELECT * FROM [westclintech].[wct].[MMULT] (
  <@Matrix_A_TableName, nvarchar(4000),>
 ,<@Matrix_A_ColumnNames, nvarchar(4000),>
 ,<@Matrix_A_GroupedColumnName, nvarchar(4000),>
 ,<@Matrix_A_GroupedColumnValue, sql_variant,>
 ,<@Matrix_B_TableName, nvarchar(4000),>
 ,<@Matrix_B_ColumnNames, nvarchar(4000),>
 ,<@Matrix_B_GroupedColumnName, nvarchar(4000),>
 ,<@Matrix_B_GroupedColumnValue, sql_variant,>)

Description

Use MMULT 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

Arguments

@Matrix_B_GroupedColumnName

the name, as text, of the column in the table or view specified by @Matrix_B_TableName which will be used for grouping the results.

@Matrix_A_TableName

the name, as text, of the table or view that contains the values in the first array to be used in the MMULT calculation.

@Matrix_B_ColumnNames

the name, as text, of the columns in the table or view specified by @Matrix_B_TableName that contains the array values to be used in the MMULT calculation. Data returned from the @Matrix_B_ColumnNames must be of the type float or of a type that implicitly converts to float.

@Matrix_A_GroupedColumnName

the name, as text, of the column in the table or view specified by @Matrix_A_TableName which will be used for grouping the results.

@Matrix_B_GroupedColumnValue

the column value to do the grouping on.

@Matrix_A_ColumnNames

the name, as text, of the columns in the table or view specified by @Matrix_A_TableName that contains the array values to be used in the MMULT calculation. Data returned from the @Matrix_A_ColumnNames must be of the type float or of a type that implicitly converts to float.

@Matrix_A_GroupedColumnValue

the column value to do the grouping on.

@Matrix_B_TableName

the name, as text, of the table or view that contains the values in the second array to be used in the MMULT calculation.

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": "6743064a-0c16-4fe2-a0b2-39fc204bb53b", "colName": "RowNum", "colDatatype": "int", "colDesc": "The zero-based row index for the matrix"}, {"id": "cbea0894-a181-42e8-b0f7-2f5648b8a573", "colName": "ColNum", "colDatatype": "int", "colDesc": "The zero-based column index for the matrix"}, {"id": "c7e6d840-48b1-4006-81cb-e59049d5b170", "colName": "ItemValue", "colDatatype": "float", "colDesc": "The value at RowNum, ColNum"}]}

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 MMULT_q function for more complex queries or to pass the matrices directly into the function without having to store them in a table.

Use MMULTN for a table 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 have created a simple table to store matrices that might have up to 10 columns in them. In practice, the 4,000-character length of @Matrix_A_ColumnNames and @Matrix_B_ColumnNames limits the number of columns that can be handled by MMULT. If you need more than 4,000 characters, use the MMULT_q function.

CREATE TABLE #m

(

    MatrixID nvarchar(5),

    rowno float,

    Col00 float,

    Col01 float,

    Col02 float,

    Col03 float,

    Col04 float,

    Col05 float,

    Col06 float,

    Col07 float,

    Col08 float,

    Col09 float

);

INSERT INTO #m

(

    Matrixid,

    rowno,

    Col00,

    col01,

    col02,

    col03

)

VALUES

('1A', 0, 5, 6, 7, 8);

INSERT INTO #m

(

    Matrixid,

    rowno,

    Col00,

    col01,

    col02,

    col03

)

VALUES

('1A', 1, 9, 10, -11, 12);

INSERT INTO #m

(

    Matrixid,

    rowno,

    Col00,

    col01,

    col02,

    col03

)

VALUES

('1A', 2, 16, 15, 14, 13);

INSERT INTO #m

(

    Matrixid,

    rowno,

    Col00,

    col01,

    col02,

    col03

)

VALUES

('1B', 0, 1, 8, 9, 13);

INSERT INTO #m

(

    Matrixid,

    rowno,

    Col00,

    col01,

    col02,

    col03

)

VALUES

('1B', 1, 2, 7, 10, 14);

INSERT INTO #m

(

    Matrixid,

    rowno,

    Col00,

    col01,

    col02,

    col03

)

VALUES

('1B', 2, -3, 6, 11, 15);

INSERT INTO #m

(

    Matrixid,

    rowno,

    Col00,

    col01,

    col02,

    col03

)

VALUES

('1B', 3, 4, 5, 12, 16);

SELECT C.*

FROM wct.MMULT(

                  '#m',

                  'col00, col01, col02, col03',

                  'MatrixID',

                  '1A',

                  '#m',

                  'col00, col01, col02, col03',

                  'MatrixID',

                  '1B'

              ) C;

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":"28"},{"RowNum":"0","ColNum":"1","ItemValue":"164"},{"RowNum":"0","ColNum":"2","ItemValue":"278"},{"RowNum":"0","ColNum":"3","ItemValue":"382"},{"RowNum":"1","ColNum":"0","ItemValue":"110"},{"RowNum":"1","ColNum":"1","ItemValue":"136"},{"RowNum":"1","ColNum":"2","ItemValue":"204"},{"RowNum":"1","ColNum":"3","ItemValue":"284"},{"RowNum":"2","ColNum":"0","ItemValue":"56"},{"RowNum":"2","ColNum":"1","ItemValue":"382"},{"RowNum":"2","ColNum":"2","ItemValue":"604"},{"RowNum":"2","ColNum":"3","ItemValue":"836"}]}

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 the number of columns in the second array).

SELECT [0],

       [1],

       [2],

       [3]

FROM

(

    SELECT C.*

    FROM wct.MMULT(

                      '#m',

                      'col00, col01, col02, col03',

                      'MatrixID',

                      '1A',

                      '#m',

                      'col00, col01, col02, col03',

                      'MatrixID',

                      '1B'

                  ) C

) M

PIVOT

(

    MAX(ItemValue)

    FOR colnum IN ([0], [1], [2], [3])

) AS pvt

ORDER BY rownum;