# SQL Server triangular lower matrix

MTRIL

Updated: 31 January 2012

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.
Syntax
SELECT [wctMath].[wct].[MTRIL](
<@Matrix, nvarchar(max),>)
Arguments
@Matrix
a string representation of a matrix.
Return Types
[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.
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 l.StringSegment
FROM wctString.wct.SPLIT(';',(SELECT wct.MTRIL(@A))) l
This produces the following result:
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

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((SELECT wct.MTRIL(@A))) l
This produces the following result.
RowNum      ColNum              ItemValue
----------- ----------- ----------------------
0           0                    -79
0           1                      0
0           2                      0
0           3                      0
0           4                      0
0           5                      0
1           0                     68
1           1                     46
1           2                      0
1           3                      0
1           4                      0
1           5                      0
2           0                     83
2           1                    -25
2           2                     80
2           3                      0
2           4                      0
2           5                      0
3           0                     77
3           1                     40
3           2                    -24
3           3                     69
3           4                      0
3           5                      0
4           0                    -17
4           1                    -72
4           2                     -9
4           3                    -72
4           4                     -6
4           5                      0
5           0                     64
5           1                    -47
5           2                     48
5           3                    -54
5           4                     18
5           5                     11
6           0                     -4
6           1                    -36
6           2                      7
6           3                    -56
6           4                    -34
6           5                     -3
7           0                    -41
7           1                     90
7           2                     78
7           3                    -43
7           4                     38
7           5                     64
8           0                    -60
8           1                    -85
8           2                    -31
8           3                    -83
8           4                    -96
8           5                    -36
9           0                    -40
9           1                     31
9           2                    -93
9           3                    -62
9           4                     64
9           5                     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((SELECT 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.

In this example, we insert the matrix values into a table, #m, which is in ‘spreadsheet’ format, and we use the MATRIX2SRTING 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.