how to multiply the first matrix by itself but transposed in SSMS 17

warlock 101 Reputation points
2021-03-07T08:24:25.54+00:00

I have matrix. Here this
create table matr1
(
x1 num,
x2 num,
x3 num,
x4 num,
x5 num,
x6 num,
x7 num
)

insert into comm values
(1,0,0,0,0,0,0,0),
(1,0,5,1,2,2,8,0,0,5,0,2),
(1,0,5,0,0,0,1,2,0),
(1,0,0,0,0,0,0,0),
(1,0,0,5,0,8,0,0,0,2),
(1,0,0,0,2,0,1,2,-12),
(1,0,5,0,5,0,1,0,-10,14),
(1,0,5,0,0,5,-1,0,10,-12),
(1,1,0,0,5,1,0,-10,13),
(1,-0,5,0,0,5,0,2,14,1),
(1,1,0,-0,5,-1,0,0,0),
(1,-0,5,0,0,0,5,-1,0,0),
(1,0,0,0,5,0,1,1,2),
(1,1,0,-0,5,-0,5,1,2,-2),
(1,0,0,0,7,0,5,-1,5,-3,8),
(1,-4,-2,-3,7,-1,5,4,5,8,4),
(1,0,1,2,0,1,2,-14),
(1,0,5,0,5,0,1,-1,-12,0),
(1,1,0,0,-1,0,5,0,0),
(1,0,0,5,1,5,0,1,5,0,0),
(1,0,5,0,0,5,0,0,0,20),
(1,0,2,5,0,-1,4,22,2),
(1,-2,-1,0,1,0,0,-4),
(1,0,5,0,5,0,1,-2,-4,10),
(1,0,-3,-2,5,-2,5,6,8,0),
(1,0,0,0,0,0,0);

then i have this matrix but it only transposed.
here it
create table matr2
(
x1 num,
x2 num,
x3 num,
x4 num,
x5 num,
x6 num,
x7 num,
x8 num,
x9 num,
x10 num,
x11 num,
x12 num,
x13 num,
x14 num,
x15 num,
x16 num,
x17 num,
x18 num,
x18 num,
x19 num,
x20 num,
x21 num,
x22 num,
x23 num,
x24 num,
x25 num,
x26 num
)
(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1),
(0,0,5,0,5,0,0,0,0,5,0,5,1,-0,5,1,-0,5,0,1,0,-4,0,0,5,1,0,0,5,0,-2,0,5,0,0),
(0,1,2,0,0,0,5,0,0,5,0,0,0,0,0,0,0,0,-2,1,0,5,0,0,5,0,2,5,-1,0,5,-3,0),
(0,2,8,0,0,0,8,0,2,0,0,5,0,5,0,5,-0,5,0,0,5,-0,5,0,7,-3,7,2,0,0,1,5,0,5,0,0,0,-2,5,0),
(0,0,0,0,0,0,1,-1,1,0,-1,0,5,0,-0,5,0,5,-1,5,0,1,-1,0,0,-1,1,1,-2,5,0),
(0,0,5,1,0,0,1,0,0,0,2,0,-1,1,1,-1,5,4,5,1,-1,0,5,1,5,0,4,0,-2,6,0),
(0,0,2,0,0,2,-10,10,-10,14,0,0,1,2,-3,8,2,-12,0,0,0,22,0,-4,8,0),
(0,0,2,0,0,2,-12,14,-12,13,1,0,0,2,-2,8,4,-14,0,0,0,20,2,-4,10,0);

how to multiply the first matrix by itself but transposed ?
that result must be something like in the picture
75093-%D1%81%D0%BD%D0%B8%D0%BC%D0%BE%D0%BA.png

Can anybody help please?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,488 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-03-07T10:45:30.647+00:00

    Let's take a couple of steps back. In a relational object, a table is supposed to model a unique entity and each column is supposed to model a unique attribute of that entity. And the operations you can perform in a relational database are designed from these principles.

    Another principle is that the data in a table is defined by the data itself, or more precisely by key columns in the database. The relational database does not offer an built-in key.

    I don't know the full story of these tables, but very little indicates that you are abiding to the principles I described above.

    So to solve this problem we first need to find out the best way to store the data. Then we start at multiplying it. In fact, with a good design, it may be self-evident how to perform the operation.

    0 comments No comments

  2. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-03-08T06:45:44.643+00:00

    Hi warlock-1010,

    You may need to change the table structure to store the matrices.
    For example: row_num corresponds to the row of the matrix, col_num corresponds to the column of the matrix & value is the value held at this cell.

    IF EXISTS (SELECT * FROM sys.tables WHERE name = 'matrixA')  
       DROP TABLE matrixA;  
    GO  
    CREATE TABLE matrixA (  
       row_num TINYINT,  
       col_num TINYINT,  
       value TINYINT  
    );  
    GO  
    IF EXISTS (SELECT * FROM sys.tables WHERE name = 'matrixB')  
       DROP TABLE matrixB;  
    GO  
    CREATE TABLE matrixB (  
       row_num TINYINT,  
       col_num TINYINT,  
       value TINYINT  
    );  
    GO  
    

    And for more details about Matrix Multiplication, please refer to Matrix Multiplication Calculated with T-SQL and Matrix Math in SQL which might help.
    Best Regards,
    Amelia


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments