SQL Server MATRIX Function

Updated 2024-03-28 19:05:40.297000

Description

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

Syntax

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

Arguments

@M

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

Return Type

table

colName	colDatatype	colDesc
RowNum	int	The zero-based row index for the matrix
ColNum	int	The zero-based column index for the matrix
ItemValue	float	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.

RowNum	ColNum	ItemValue
0	0	1
0	1	2
0	2	3
0	3	4
0	4	5
0	5	6
0	6	7
1	0	8
1	1	9
1	2	10
1	3	11
1	4	12
1	5	13
1	6	14
2	0	15
2	1	16
2	2	17
2	3	18
2	4	19
2	5	20
2	6	21
3	0	22
3	1	23
3	2	24
3	3	25
3	4	26
3	5	27
3	6	28

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