SQL Server MCROSS Function

Updated 2023-10-16 20:22:32.930000

Description

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 3^rd normal form. Enter 'False' for a de-normalized table in 'spreadsheet' form.

Return Type

table

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 3^rd 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 3^rd 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

CROSSPROD - scalar function to calculate A' * A or A' * B

MATMULT - matrix multiplication for string representations of matrices

MMULT_Q - Calculate the matrix product of two arrays.

TCROSSPROD - scalar function to calculate A * A' or A * B'

TMCROSS - table-valued function to calculate A * A' or A * B'

TRANSPOSE - scalar function to convert A to A'