MTRIU
Updated: 31 January 2012
Use the scalar function MTRIU to return the upper triangular part of the string representation of a matrix.
MTRIU expects a string representation of the matrix, with columns separated by commas and rows separated by semi-colons.
Syntax
SELECT [wctMath].[wct].[MTRIU](
<@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 upper 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.MTRIU(@A) as [U]
This produces the following result.
U
-----------------------------------------------------------------------------
-79,-45,9,9,-91,-5;0,46,9,81,-61,35;0,0,80,-67,-22,-38;0,0,0,69,73,-20;0,0,0,0,-6,-34;0,0,0,0,0,11;0,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0
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 as [U]
FROM wctString.wct.SPLIT(';',(SELECT wct.MTRIU(@A))) l
This produces the following result:
U
-----------------------------------------
-79,-45,9,9,-91,-5
0,46,9,81,-61,35
0,0,80,-67,-22,-38
0,0,0,69,73,-20
0,0,0,0,-6,-34
0,0,0,0,0,11
0,0,0,0,0,0
0,0,0,0,0,0
0,0,0,0,0,0
0,0,0,0,0,0
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.MTRIU(@A))) l
This produces the following result.
RowNum ColNum ItemValue
----------- ----------- ----------------------
0 0 -79
0 1 -45
0 2 9
0 3 9
0 4 -91
0 5 -5
1 0 0
1 1 46
1 2 9
1 3 81
1 4 -61
1 5 35
2 0 0
2 1 0
2 2 80
2 3 -67
2 4 -22
2 5 -38
3 0 0
3 1 0
3 2 0
3 3 69
3 4 73
3 5 -20
4 0 0
4 1 0
4 2 0
4 3 0
4 4 -6
4 5 -34
5 0 0
5 1 0
5 2 0
5 3 0
5 4 0
5 5 11
6 0 0
6 1 0
6 2 0
6 3 0
6 4 0
6 5 0
7 0 0
7 1 0
7 2 0
7 3 0
7 4 0
7 5 0
8 0 0
8 1 0
8 2 0
8 3 0
8 4 0
8 5 0
9 0 0
9 1 0
9 2 0
9 3 0
9 4 0
9 5 0
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.MTRIU(@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 MTRIU 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.MTRIU(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.