# SQL Server element-wise matrix addition

Updated: 31 January 2012

Use the scalar function MATADD to add a value to every element of a matrix or to add 2 matrices of the same dimensions together.
MATADD expects a string representation of the matrices, with columns separated by commas and rows separated by semi-colons.
Syntax
<@Matrix1, nvarchar(max),>
,<@Matrix2, nvarchar(max),>)
Arguments
@Matrix1
a string representation of a matrix.
@Matrix2
a string representation of a matrix.
Return Types
[nvarchar](max)
Remarks
·         If @Matrix1 contains more than one element and @Matrix2 contains more than one element, then the number of rows and columns in @Matrix1 must be equal to the number of rows and columns in @Matrix2 or an error will be returned.
·         The string representations of @Matrix1 and @Matrix2 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.
·         To convert the string result to a table, us the table-valued function MATRIX.
·         To subtract 2 matrices, use the MATSUB function.
Examples
Let’s assume that we had the following matrices, A & B, and we wanted to calculate A + B:
A = [-30,-39,64,100;-81,-62,40,-10;-66,-59,-99,-57;34,37,5,22;40,9,68,-5]
B = [20,41,-46,2;-47,0,54,41;38,-70,-16,95;4,77,70,-81;47,-41,-55,-46]
We could enter the following SQL to perform the addition.
DECLARE @A as varchar(max)
DECLARE @B as varchar(max)

SET @A = '-30,-39,64,100;-81,-62,40,-10;-66,-59,-99,-57;34,37,5,22;40,9,68,-5'
SET @B = '20,41,-46,2;-47,0,54,41;38,-70,-16,95;4,77,70,-81;47,-41,-55,-46'

SELECT wct.MATADD(@A, @B) as [A+B]
This produces the following result.
A+B
------------------------------------------------------------------------
-10,2,18,102;-128,-62,94,31;-28,-129,-115,38;38,114,75,-59;87,-32,13,-51
Because this addition, order does not matter, so if we switch the matrix in the function, we wrill get the same result.
SELECT wct.MATADD(@B,@A) as [B+A]
This produces the following result.
B+A
------------------------------------------------------------------------
-10,2,18,102;-128,-62,94,31;-28,-129,-115,38;38,114,75,-59;87,-32,13,-51
The matrices do not have to assigned variables before passed into the MATADD function; the strings can be passed in directly.
SELECT wct.MATADD('-30,-39,64,100;-81,-62,40,-10;-66,-59,-99,-57;34,37,5,22;40,9,68,-5', '20,41,-46,2;-47,0,54,41;38,-70,-16,95;4,77,70,-81;47,-41,-55,-46') as [A+B]
This produces the following result.
A+B
------------------------------------------------------------------------
-10,2,18,102;-128,-62,94,31;-28,-129,-115,38;38,114,75,-59;87,-32,13,-51
In this example, we are going to add 5 to every element in the @A matrix.
DECLARE @A as varchar(max)
DECLARE @B as VARCHAR(max)

SET @A = '-30,-39,64,100;-81,-62,40,-10;-66,-59,-99,-57;34,37,5,22;40,9,68,-5'
SET @B = '5'

SELECT wct.MATADD(@A,@B) as [A+B]
This produces the following result.
A+B
-------------------------------------------------------------------------
-25,-34,69,105;-76,-57,45,-5;-61,-54,-94,-52;39,42,10,27;45,14,73,0
If we switched the order of the matrices we get the same result.
SELECT wct.MATADD(@B,@A) as [B+A]
This produces the following result.
B+A
-------------------------------------------------------------------
-25,-34,69,105;-76,-57,45,-5;-61,-54,-94,-52;39,42,10,27;45,14,73,0
In this example, the matrix values are stored on a table in the database and are converted to a string value using the MATRIX2STRING function.
/* Put A into a table */
SELECT *
INTO #A
FROM (
SELECT -30,-39,64,100 UNION ALL
SELECT -81,-62,40,-10 UNION ALL
SELECT -66,-59,-99,-57 UNION ALL
SELECT 34,37,5,22 UNION ALL
SELECT 40,9,68,-5
) A(xo,x1,x2,x3)
/* Put B into a table */
SELECT *
INTO #B
FROM (
SELECT 20,41,-46,2 UNION ALL
SELECT -47,0,54,41 UNION ALL
SELECT 38,-70,-16,95 UNION ALL
SELECT 4,77,70,-81 UNION ALL
SELECT 47,-41,-55,-46
) B(x0,x1,x2,x3)
/* Do the matrix addition */
SELECT wct.MATADD(wct.MATRIX2STRING('#A','*','',NULL), wct.MATRIX2STRING('#B','*','',NULL)) as [A+B]
This produces the following result.
A+B
------------------------------------------------------------------------
-10,2,18,102;-128,-62,94,31;-28,-129,-115,38;38,114,75,-59;87,-32,13,-51
If we wanted to return the matrix sum as a normalized table, we can use the table-valued function MATRIX to do that.
SELECT *
This produces the following result.
RowNum      ColNum              ItemValue
----------- ----------- ----------------------
0           0                    -10
0           1                      2
0           2                     18
0           3                    102
1           0                   -128
1           1                    -62
1           2                     94
1           3                     31
2           0                    -28
2           1                   -129
2           2                   -115
2           3                     38
3           0                     38
3           1                    114
3           2                     75
3           3                    -59
4           0                     87
4           1                    -32
4           2                     13
4           3                    -51
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 *
) M PIVOT(
MAX(ItemValue)
FOR colnum IN([0],[1],[2],[3])
) AS pvt
ORDER BY rownum
This produces the following result.