Logo
Getting started

MTRIL

Updated 2023-10-17 14:29:36.847000

Syntax

SELECT [westclintech].[wct].[MTRIL](
   <@Matrix, nvarchar(max),>)

Description

Use the scalar function MTRIL to return the lower triangular part of the string representation of a matrix.

MTRIL expects a string representation of the matrix, with columns separated by commas and rows separated by semi-colons.

Arguments

@Matrix

a string representation of a matrix.

Return Type

nvarchar(max)

Remarks

The string representations of @Matrix must only contain numbers, commas (to separate the columns), and semi-colons to separate the rows.

Consecutive commas will generate an error.

Consecutive semi-colons will generate an error.

Non-numeric data between commas will generate an error

Non-number data between semi-colons will generate an error

To convert non-normalized data to a string format, use the Matrix2String or the Matrix2String_q function.

To convert normalized data to a string format, use the NMatrix2String or the NMatrix2String_q function.

Examples

Let’s assume that we had the following matrix, A, and we want to return the lower triangular part.

A = [-79,-45,9,9,-91,-5;68,46,9,81,-61,35;83,-25,80,-67,-22,-38;77,40,-24,69,73,-20;-17,-72,-9,-72,-6,-34;64,-47,48,-54,18,11;-4,-36,7,-56,-34,-3;-41,90,78,-43,38,64;-60,-85,-31,-83,-96,-36;-40,31,-93,-62,64,10]

We could enter the following SQL to perform the calculation.

DECLARE @A as varchar(max);

SET @A

    = '-79,-45,9,9,-91,-5;68,46,9,81,-61,35;83,-25,80,-67,-22,-38;77,40,-24,69,73,

              -20;-17,-72,-9,-72,-6,-34;64,-47,48,-54,18,11;-4,-36,7,-56,-34,-3;

              -41,90,78,-43,38,64;-60,-85,-31,-83,-96,-36;-40,31,-93,-62,64,10';

SELECT wct.MTRIL(@A) as [L];

This produces the following result.

{"columns":[{"field":"L"}],"rows":[{"L":"-79,0,0,0,0,0;68,46,0,0,0,0;83,-25,80,0,0,0;77,40,-24,69,0,0;-17,-72,-9,-72,-6,0;64,-47,48,-54,18,11;-4,-36,7,-56,-34,-3;-41,90,78,-43,38,64;-60,-85,-31,-83,-96,-36;-40,31,-93,-62,64,10"}]}

Of course, this is a little hard to read. Since the result is a string, we can reformat the solution to make it easier to read. Simply by changing the SELECT statement:

SELECT item

FROM wct.SPLIT(wct.MTRIL(@A), ';') l;

This produces the following result.

{"columns":[{"field":"item"}],"rows":[{"item":"-79,0,0,0,0,0"},{"item":"68,46,0,0,0,0"},{"item":"83,-25,80,0,0,0"},{"item":"77,40,-24,69,0,0"},{"item":"-17,-72,-9,-72,-6,0"},{"item":"64,-47,48,-54,18,11"},{"item":"-4,-36,7,-56,-34,-3"},{"item":"-41,90,78,-43,38,64"},{"item":"-60,-85,-31,-83,-96,-36"},{"item":"-40,31,-93,-62,64,10"}]}

Which is a little bit easier to follow

However, we can use the table-valued function MATRIX, to format the result in third-normal form where it is even easier to see the output.

SELECT *

FROM wct.MATRIX(wct.MTRIL(@A)) l;

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":"-79"},{"RowNum":"0","ColNum":"1","ItemValue":"0"},{"RowNum":"0","ColNum":"2","ItemValue":"0"},{"RowNum":"0","ColNum":"3","ItemValue":"0"},{"RowNum":"0","ColNum":"4","ItemValue":"0"},{"RowNum":"0","ColNum":"5","ItemValue":"0"},{"RowNum":"1","ColNum":"0","ItemValue":"68"},{"RowNum":"1","ColNum":"1","ItemValue":"46"},{"RowNum":"1","ColNum":"2","ItemValue":"0"},{"RowNum":"1","ColNum":"3","ItemValue":"0"},{"RowNum":"1","ColNum":"4","ItemValue":"0"},{"RowNum":"1","ColNum":"5","ItemValue":"0"},{"RowNum":"2","ColNum":"0","ItemValue":"83"},{"RowNum":"2","ColNum":"1","ItemValue":"-25"},{"RowNum":"2","ColNum":"2","ItemValue":"80"},{"RowNum":"2","ColNum":"3","ItemValue":"0"},{"RowNum":"2","ColNum":"4","ItemValue":"0"},{"RowNum":"2","ColNum":"5","ItemValue":"0"},{"RowNum":"3","ColNum":"0","ItemValue":"77"},{"RowNum":"3","ColNum":"1","ItemValue":"40"},{"RowNum":"3","ColNum":"2","ItemValue":"-24"},{"RowNum":"3","ColNum":"3","ItemValue":"69"},{"RowNum":"3","ColNum":"4","ItemValue":"0"},{"RowNum":"3","ColNum":"5","ItemValue":"0"},{"RowNum":"4","ColNum":"0","ItemValue":"-17"},{"RowNum":"4","ColNum":"1","ItemValue":"-72"},{"RowNum":"4","ColNum":"2","ItemValue":"-9"},{"RowNum":"4","ColNum":"3","ItemValue":"-72"},{"RowNum":"4","ColNum":"4","ItemValue":"-6"},{"RowNum":"4","ColNum":"5","ItemValue":"0"},{"RowNum":"5","ColNum":"0","ItemValue":"64"},{"RowNum":"5","ColNum":"1","ItemValue":"-47"},{"RowNum":"5","ColNum":"2","ItemValue":"48"},{"RowNum":"5","ColNum":"3","ItemValue":"-54"},{"RowNum":"5","ColNum":"4","ItemValue":"18"},{"RowNum":"5","ColNum":"5","ItemValue":"11"},{"RowNum":"6","ColNum":"0","ItemValue":"-4"},{"RowNum":"6","ColNum":"1","ItemValue":"-36"},{"RowNum":"6","ColNum":"2","ItemValue":"7"},{"RowNum":"6","ColNum":"3","ItemValue":"-56"},{"RowNum":"6","ColNum":"4","ItemValue":"-34"},{"RowNum":"6","ColNum":"5","ItemValue":"-3"},{"RowNum":"7","ColNum":"0","ItemValue":"-41"},{"RowNum":"7","ColNum":"1","ItemValue":"90"},{"RowNum":"7","ColNum":"2","ItemValue":"78"},{"RowNum":"7","ColNum":"3","ItemValue":"-43"},{"RowNum":"7","ColNum":"4","ItemValue":"38"},{"RowNum":"7","ColNum":"5","ItemValue":"64"},{"RowNum":"8","ColNum":"0","ItemValue":"-60"},{"RowNum":"8","ColNum":"1","ItemValue":"-85"},{"RowNum":"8","ColNum":"2","ItemValue":"-31"},{"RowNum":"8","ColNum":"3","ItemValue":"-83"},{"RowNum":"8","ColNum":"4","ItemValue":"-96"},{"RowNum":"8","ColNum":"5","ItemValue":"-36"},{"RowNum":"9","ColNum":"0","ItemValue":"-40"},{"RowNum":"9","ColNum":"1","ItemValue":"31"},{"RowNum":"9","ColNum":"2","ItemValue":"-93"},{"RowNum":"9","ColNum":"3","ItemValue":"-62"},{"RowNum":"9","ColNum":"4","ItemValue":"64"},{"RowNum":"9","ColNum":"5","ItemValue":"10"}]}

And, if we wanted to see the result in a row/column presentation, we could use the following SQL.

SELECT [0],

       [1],

       [2],

       [3],

       [4],

       [5]

FROM

(SELECT * FROM wct.MATRIX(wct.MTRIL(@A)) ) M

PIVOT

(

    MAX(ItemValue)

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

) AS pvt

ORDER BY rownum;

This produces the following result.

{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"5","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"-79","1":"0","2":"0","3":"0","4":"0","5":"0"},{"0":"68","1":"46","2":"0","3":"0","4":"0","5":"0"},{"0":"83","1":"-25","2":"80","3":"0","4":"0","5":"0"},{"0":"77","1":"40","2":"-24","3":"69","4":"0","5":"0"},{"0":"-17","1":"-72","2":"-9","3":"-72","4":"-6","5":"0"},{"0":"64","1":"-47","2":"48","3":"-54","4":"18","5":"11"},{"0":"-4","1":"-36","2":"7","3":"-56","4":"-34","5":"-3"},{"0":"-41","1":"90","2":"78","3":"-43","4":"38","5":"64"},{"0":"-60","1":"-85","2":"-31","3":"-83","4":"-96","5":"-36"},{"0":"-40","1":"31","2":"-93","3":"-62","4":"64","5":"10"}]}

In this example, we insert the matrix values into a table, #m, which is in ‘spreadsheet’ format, and we use the MATRIX2STRING function to convert the table values into a string format to be used by the MTRIL function.

SELECT *
INTO #m
FROM (
      SELECT -79,-45, 9, 9,-91, -5 UNION ALL
      SELECT 68, 46, 9, 81,-61, 35 UNION ALL
      SELECT 83,-25, 80,-67,-22,-38 UNION ALL
      SELECT 77, 40,-24, 69, 73,-20 UNION ALL
      SELECT -17,-72, -9,-72, -6,-34 UNION ALL
      SELECT 64,-47, 48,-54, 18, 11 UNION ALL
      SELECT -4,-36, 7,-56,-34, -3 UNION ALL
      SELECT -41, 90, 78,-43, 38, 64 UNION ALL
      SELECT -60,-85,-31,-83,-96,-36 UNION ALL
      SELECT -40, 31,-93,-62, 64, 10
      ) n(x0,x1,x2,x3,x4,x5);
 
SELECT [0],[1],[2],[3],[4],[5]
FROM (
      SELECT *
      FROM wct.MATRIX((SELECT wct.MTRIL(wct.MATRIX2STRING('#m','*','',NULL))))
) M PIVOT(
    MAX(ItemValue)
    FOR colnum IN([0],[1],[2],[3],[4],[5])
) AS pvt
ORDER BY rownum;

This produces the following result.

{"columns":[{"field":"0","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"5","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"0":"-79","1":"0","2":"0","3":"0","4":"0","5":"0"},{"0":"68","1":"46","2":"0","3":"0","4":"0","5":"0"},{"0":"83","1":"-25","2":"80","3":"0","4":"0","5":"0"},{"0":"77","1":"40","2":"-24","3":"69","4":"0","5":"0"},{"0":"-17","1":"-72","2":"-9","3":"-72","4":"-6","5":"0"},{"0":"64","1":"-47","2":"48","3":"-54","4":"18","5":"11"},{"0":"-4","1":"-36","2":"7","3":"-56","4":"-34","5":"-3"},{"0":"-41","1":"90","2":"78","3":"-43","4":"38","5":"64"},{"0":"-60","1":"-85","2":"-31","3":"-83","4":"-96","5":"-36"},{"0":"-40","1":"31","2":"-93","3":"-62","4":"64","5":"10"}]}