Matrix multiplication

Hi there,

I have an exciting challenge to simulate matrix multiplication:
(just a crude illustration to refresh our memories)
[ a11 a12] X [b11 b22] = [a11 x b11 + a12 x b12]
[ a21 a22] [a21 x b11 + a22 x b12]

the end result of such a calculation yields a table.

Before I start writing the code for my solution, I was hoping that there might be a sql server function that does matrix multiplication, in other words, table1 x table2. Excel has a function (MMULT it think) to do this so i wonder whether sql server does this as well.

Thanks in advance
Mike

No there is no pre-defined functions available.You have to write a query(might be a complex one)...

I recommand to utilize the UI features (if you use dataset you can achive the matrix multiplication with more efficient)

|||There's no MMULT function, but here's an old post of mine with SQL to multipliy matrices:

http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/984c92525c049374/b6722c498551c734?lnk=st&q=skass+sqlserver+matrix&rnum=3#b6722c498551c734

Here's the code, with one of the display sections rewritten with PIVOT, which can be used in SQL Server 2005:

Code Snippet

SET NOCOUNT ON
CREATE TABLE Matrices (
MatrixNo int,
row int,
col int,
val int
)

INSERT INTO Matrices VALUES (1,1,1,1)
INSERT INTO Matrices VALUES (1,1,2,2)
INSERT INTO Matrices VALUES (1,1,3,0)
INSERT INTO Matrices VALUES (1,2,1,2)
INSERT INTO Matrices VALUES (1,2,2,1)
INSERT INTO Matrices VALUES (1,2,3,1)
INSERT INTO Matrices VALUES (1,3,1,0)
INSERT INTO Matrices VALUES (1,3,2,1)
INSERT INTO Matrices VALUES (1,3,3,1)

INSERT INTO Matrices VALUES (2,1,1,2)
INSERT INTO Matrices VALUES (2,1,2,3)
INSERT INTO Matrices VALUES (2,1,3,1)
INSERT INTO Matrices VALUES (2,2,1,4)
INSERT INTO Matrices VALUES (2,2,2,0)
INSERT INTO Matrices VALUES (2,2,3,0)
INSERT INTO Matrices VALUES (2,3,1,0)
INSERT INTO Matrices VALUES (2,3,2,1)
INSERT INTO Matrices VALUES (2,3,3,1)

CREATE UNIQUE CLUSTERED INDEX Matrices_mrc ON Matrices(MatrixNo, row, col)

PRINT 'Matrix 1'

SELECT
CAST('R'+convert(varchar,row) AS VARCHAR(6)) AS [\],
[1] as C1, [2] as C2, [3] as C3
FROM (
SELECT row, val, col, MatrixNo
FROM Matrices
) M PIVOT (
MAX(val)
FOR col IN ([1], [2], [3])
) AS pvt
WHERE MatrixNo = 1
ORDER BY row

SELECT CAST('R'+convert(varchar,row) AS VARCHAR(6)) AS [\],
MAX(CASE WHEN col=1 THEN val END) AS [C1],
MAX(CASE WHEN col=2 THEN val END) AS [C2],
MAX(CASE WHEN col=3 THEN val END) AS [C3]
FROM Matrices
WHERE MatrixNo=1
GROUP BY row

PRINT 'Matrix 2'
SELECT CAST('R'+convert(varchar,row) AS VARCHAR(6)) AS [\],
MAX(CASE WHEN col=1 THEN val END) AS [C1],
MAX(CASE WHEN col=2 THEN val END) AS [C2],
MAX(CASE WHEN col=3 THEN val END) AS [C3]
FROM Matrices
WHERE MatrixNo=2
GROUP BY row

PRINT 'Matrix 1 * Matrix 2'
SELECT CAST('R'+convert(varchar,row) AS VARCHAR(6)) AS [\],
MAX(CASE WHEN col=1 THEN val END) AS [C1],
MAX(CASE WHEN col=2 THEN val END) AS [C2],
MAX(CASE WHEN col=3 THEN val END) AS [C3]
FROM (
SELECT A.row, B.col, SUM(A.val*B.val) AS val
FROM Matrices A JOIN Matrices B
ON A.MatrixNo = 1
AND B.MatrixNo = 2
AND A.col = B.row
GROUP BY A.row, B.col
) ProductMatrix
GROUP BY row
GO

DROP TABLE Matrices

Steve Kass
Drew University
http://www.stevekass.com
|||Thanks for the swift replies,

I have taken a look at your code and it makes sense. I am also going to create 2 temp matrix tables and from there multiply them together in order to get a third table which would be the result as yours is above.

Regards
Mike