MCROSS
Updated: 10 April 2015
Use the table-valued function MCROSS to calculate the matrix cross-product of 2 matrices. When only one matrix is passed into the function the result is A' * A, otherwise the result is A' * B.
Syntax
SELECT * FROM [wct].[MCROSS](
<@A, nvarchar(max),>
,<@B, nvarchar(max),>
,<@Is3N, bit,>)
Arguments
@A
the SELECT statement which returns the resultant table containing the A matrix.
@B
the SELECT statement which returns the resultant table containing the B matrix.
@Is3N
a bit value identifying the form for the resultant table returned by @A and @B. Enter 'True' for a resultant table in 3rd normal form. Enter 'False' for a de-normalized table in 'spreadsheet' form.
Return Types
TABLE (
[RowNum] [int] NULL,
[ColNum] [int] NULL,
[ItemValue] [float] NULL
)
Remarks
· If @Is3N is NULL then @Is3N = 'False'.
· If @Is3N is'True' then the result table should be returned as row, column, and value.
· If the resultant table contains NULL, then NULL will be returned.
· The function returns an error if the array contains a non-numeric value.
· If @B IS NULL then the function computes A' * A.
· If @B IS NOT NULL then the number of rows in @A must equal to the number of rows in @B.
· If @B IS NULL then the function returns an n-by-n matrix where n is the number of columns in @A.
· If @B IS NOT NULL then the function returns an n-by-p matrix where n is the number of columns in @A and p is the number of columns in @B.
Examples
In this example we calculate A' * A using a matrix in spreadsheet format.
SELECT
*
FROM
wct.MCROSS('SELECT
*
FROM (VALUES
(1,1,1,1),
(1,2,4,8),
(1,3,9,27),
(1,4,16,64),
(1,5,25,125)
)n(x1,x2,x3,x4)'
,NULL
,'False'
)
This produces the following result.
In this example, we supply the same matrix, except that it is now in 3rd normal form.
SELECT
*
FROM
wct.MCROSS('
SELECT
r.n,
c.n,
POWER(r.n,c.n)
FROM (VALUES (1),(2),(3),(4),(5))r(n)
CROSS APPLY (VALUES (0),(1),(2),(3))c(n)
ORDER BY 1,2'
,NULL
,'True'
)
This produces the following results.
In this example we supply both an A and a B matrix in spreadsheet form and calculate A' * B. We will do this by creating 2 temporary tables for the A and the B matrix and then selecting from the tables in the MCROSS function.
--Create matrix A
SELECT
r.n as rn,
POWER(r.n,0) as x0,
POWER(r.n,1) as x1,
POWER(r.n,2) as x2,
POWER(r.n,3) as x3
INTO
#A
FROM (VALUES (1),(2),(3),(4),(5))r(n)
--Create matrix B
SELECT
r.n as rn,
1/POWER(cast(r.n as float),1) as x0,
1/POWER(cast(r.n as float),2) as x1,
1/POWER(cast(r.n as float),3) as x2
INTO
#B
FROM (VALUES (1),(2),(3),(4),(5))r(n)
--A' * B
SELECT
*
FROM
wct.MCROSS(
'SELECT x0,x1,x2,x3 FROM #A order by rn',
'SELECT x0,x1,x2 FROM #B order by rn',
'False'
)
This produces the following result.
Performing the same calculation as in the previous example, except that this time the data in matrices A and B are in 3rd normal form.
--Create the A Matrix
SELECT
r.n as rowno,
c.n as colno,
POWER(r.n,c.n) as x
INTO
#A
FROM (VALUES
(1),(2),(3),(4),(5))r(n)
CROSS APPLY(VALUES
(0),(1),(2),(3))c(n)
--Create the B Matrix
SELECT
r.n as rowno,
c.n as colno,
1/POWER(cast(r.n as float),c.n) as x
INTO
#B
FROM (VALUES
(1),(2),(3),(4),(5))r(n)
CROSS APPLY(VALUES
(1),(2),(3))c(n)
--A' * B
SELECT
*
FROM
wct.MCROSS(
'SELECT * FROM #A order by 1,2',
'SELECT * FROM #B order by 1,2',
'True'
)
This produces the following result.
See Also