SQL Server QR decomposition

QR

Updated: 31 January 2012

Use QR for decomposing a the string representation of an N x N matrix A into the product of an upper triangular matrix R and an orthogonal matrix Q, such that
A=QR.
QR expects a string representation of the matrix, with columns separated by commas and rows separated by semi-colons.
SELECT [wctMath].[wct].[QR](
<@Matrix, nvarchar(max),>
,<@MatrixName, nvarchar(4000),>)
GO
Arguments
@Matrix
a string representation of an upper-triangular matrix.
@MatrixName
The name of the matrix, Q or R, to be returned.
Return Types
[nvarchar](max)
Remarks
·         The number of columns in @Matrix must be equal to the number of rows or an error will be returned.
·         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.
·         To convert the string result to a table, use the table-valued function MATRIX.
·         If @MatrixName is not ‘Q’ or ‘R’ an error message will be returned.
Examples
In this example we will return the Q matrix in string format.
DECLARE @A as varchar(max)

SET @A = '12,-51,4;6,167,-68;-4,24,-41'

SELECT wct.QR(@A,'Q') as Q
This produces the following result.
Q
-----------------------------------------------------------------------------
-0.857142857142857,0.394285714285714,-0.331428571428571;
-0.428571428571429,-0.902857142857142,0.0342857142857143;
0.285714285714286,-0.171428571428571,-0.942857142857142

To get the R matrix, we could have entered:
SELECT wct.QR(@A,'R') as R
This produces the following result.
R
-----------------------------------------------------------------------------
-14,-21,14;0,-175,70;0,0,35
If we calculate Q * R we should get the original matrix, A, returned, within the limits of floating point arithmetic.
DECLARE @A as varchar(max)

SET @A = '12,-51,4;6,167,-68;-4,24,-41'

SELECT wct.MATMULT(wct.QR(@A,'Q'),wct.QR(@A,'R')) as [Q * R]
This produces the following result.
Q * R
-----------------------------------------------------------------------------
12,-51,4;6.00000000000001,167,-67.9999999999999;-4,23.9999999999999,-40.9999999999999
We can use the table-valued function MATRIX to turn the results into third-normal form.
SELECT RowNum
,ColNum
,ROUND(ItemValue, 0) as ItemValue
FROM wct.MATRIX(wct.MATMULT(wct.QR(@A,'Q'),wct.QR(@A,'R')))
This produces the following result.
RowNum      ColNum              ItemValue
----------- ----------- ----------------------
0           0                     12
0           1                    -51
0           2                      4
1           0                      6
1           1                    167
1           2                    -68
2           0                     -4
2           1                     24
2           2                    -41

We can also calculate QTQ, verifying that this will return the identity matrix.
DECLARE @A as varchar(max)

SET @A = '12,-51,4;6,167,-68;-4,24,-41'

SELECT RowNum
,ColNum
,ROUND(ItemValue, 0) as ItemValue
FROM wct.MATRIX(wct.MATMULT(wct.TRANSPOSE(wct.QR(@A,'Q')),wct.QR(@A,'Q')))
This returns the following result.
RowNum      ColNum              ItemValue
----------- ----------- ----------------------
0           0                      1
0           1                      0
0           2                      0
1           0                      0
1           1                      1
1           2                      0
2           0                      0
2           1                      0
2           2                      1