question

warlock-1010 avatar image
0 Votes"
warlock-1010 asked ·

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

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-general
снимок.png (10.2 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered ·

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.


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.