Home » SQL & PL/SQL » SQL & PL/SQL » matrix multiplication (Oracle 10g, Windows XP)
matrix multiplication Mon, 08 February 2010 07:55
 joshua82 Messages: 31Registered: December 2009 Member
Hi! I need to do a matrix multiplication in Oracle 10g (like this).

Consider a situation like this:
```CREATE TABLE A(
rows in NUMBER NOT NULL,
A in NUMBER NOT NULL,
B in NUMBER NOT NULL,
C in NUMBER NOT NULL,
D in NUMBER NOT NULL,
E in NUMBER NOT NULL,
F in NUMBER NOT NULL,
G in NUMBER NOT NULL,
H in NUMBER NOT NULL
)
INSERT INTO A VALUES(1, -3, 5, -2, 8, 6, 8, 9, 0);
INSERT INTO A VALUES(2, 3, 8, 6, 4, 9, 3, 6, -7);
INSERT INTO A VALUES(3, 2, 8, 9, 3, 4, 3, 7, 9);
INSERT INTO A VALUES(4, 1, -4, 4, 5, 7, 9, 9, 5);
INSERT INTO A VALUES(5, 7, 6, 2, -9, 7, 9, 9, 6);
INSERT INTO A VALUES(6, 5, 2, 9, 6, 5, 8, 6, 8);
INSERT INTO A VALUES(7, 4, 0, 2, 5, 9, 5, 8, 3);
--==============================================

CREATE TABLE B(
rows in NUMBER NOT NULL,
A in NUMBER NOT NULL,
B in NUMBER NOT NULL,
C in NUMBER NOT NULL,
D in NUMBER NOT NULL,
E in NUMBER NOT NULL,
F in NUMBER NOT NULL,
G in NUMBER NOT NULL,
H in NUMBER NOT NULL,
I in NUMBER NOT NULL
)
INSERT INTO B VALUES(1, 18, 5, 12, 8, 0, 8, 9, -2, 1);
INSERT INTO B VALUES(2, 0, 8, -6, 4, -2, 3, 6, 5, 6);
INSERT INTO B VALUES(3, 2 , 8, 9, 3, 4, 3, 7, 4, 9);
INSERT INTO B VALUES(4, 1, 9, 0, 5, 7, 9, -9, 5, 3);
INSERT INTO B VALUES(5, 7, 6, 2, 9, 7, 9, 9, 6, 8);
INSERT INTO B VALUES(6, 5, 0, 9, 6, 5, 8, 6, 8, 9);
INSERT INTO B VALUES(7, 0, 4, 2, 5, 9, 0, 8, 6, 1);
INSERT INTO B VALUES(8, 5, 9, 5, 3, 7, -3, 0, 2, 6);

------------------- Matrix A -----------------------------

rows     1     2     3     4     5     6     7     8
--------------------------------------------------------
1       -3     5    -2     8     6     8     9     0
2        3     8     6     4     9     3     6    -7
3        2     8     9     3     4     3     7     9
4        1    -4     4     5     7     9     9     5
5        7     6     2    -9     7     9     9     6
6        5     2     9     6     5     8     6     8
7        4     0     2     5     9     5     8     3

---------------- Matrix B ----------------------------

rows     1     2     3     4     5     6     7     8     9
--------------------------------------------------------------
1       18     5    12     8     0     8     9    -2     1
2        0     8    -6     4    -2     3     6     5     6
3        2     8     9     3     4     3     7     4     9
4        1     9     0     5     7     9    -9     5     3
5        7     6     2     9     7     9     9     6     8
6        5     0     9     6     5     8     6     8     9
7        0     4     2     5     9     0     8     6     1
8        5     9     5     3     7    -3     0     2     6```

I found a solution in SQL (here), but I need to do that in PL/SQL.
Thanks

[Updated on: Mon, 08 February 2010 07:56]

Report message to a moderator

Re: matrix multiplication [message #442497 is a reply to message #442485] Mon, 08 February 2010 09:46
 Michel Cadot Messages: 65085Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
You have to first know how to order the rows in the tables.
Which one is the first one? and so on.
If you have no column to order the rows then it is not possible.

Regards
Michel
Re: matrix multiplication [message #442499 is a reply to message #442497] Mon, 08 February 2010 10:11
 joshua82 Messages: 31Registered: December 2009 Member
Michel Cadot wrote on Mon, 08 February 2010 09:46
You have to first know how to order the rows in the tables.

The incremental number of row isn't an order?
Re: matrix multiplication [message #442500 is a reply to message #442485] Mon, 08 February 2010 10:14
 _jum Messages: 515Registered: February 2008 Senior Member
Here is the example in a version for ORACLE 11.
The code is very near to the original, guess there are more effective ways to solve the problem:
```DROP TABLE TESTMAT_A;

DROP TABLE TESTMAT_B;
--=====================================

CREATE TABLE TESTMAT_A(
nr INTEGER NOT NULL,
v1 INTEGER NOT NULL,
v2 INTEGER NOT NULL,
v3 INTEGER NOT NULL,
v4 INTEGER NOT NULL,
v5 INTEGER NOT NULL
);

INSERT INTO TESTMAT_A VALUES(1, -3, 5, -2, 8, 6);
INSERT INTO TESTMAT_A VALUES(2, 3, 8, 6, 4, 9);
INSERT INTO TESTMAT_A VALUES(3, 2, 8, 9, 3, 4);
INSERT INTO TESTMAT_A VALUES(4, 1, -4, 4, 5, -1);
INSERT INTO TESTMAT_A VALUES(5, 7, 6, 2, -9, 0);

--=====================================

CREATE TABLE TESTMAT_B(
nr INTEGER NOT NULL,
v1 INTEGER NOT NULL,
v2 INTEGER NOT NULL,
v3 INTEGER NOT NULL,
v4 INTEGER NOT NULL,
v5 INTEGER NOT NULL
);

INSERT INTO TESTMAT_B VALUES(1, 18, 5, 12, 8, 0);
INSERT INTO TESTMAT_B VALUES(2, 0, 8, -6, 4, -2);
INSERT INTO TESTMAT_B VALUES(3, 2 , 8, 9, 3, 4);
INSERT INTO TESTMAT_B VALUES(4, 1, 9, 0, 5, 7);
INSERT INTO TESTMAT_B VALUES(5, 7, 6, 2, 9, 7);

--=====================================

SELECT * FROM TESTMAT_A;

SELECT * FROM TESTMAT_B;

/*
--=========== Matrice TESTMAT_A ==============
--        NR         V1         V2         V3         V4         V5
------------ ---------- ---------- ---------- ---------- ----------
--         1         -3          5         -2          8          6
--         2          3          8          6          4          9
--         3          2          8          9          3          4
--         4          1         -4          4          5         -1
--         5          7          6          2         -9          0

--============ Matrice TESTMAT_B =============
--        NR         V1         V2         V3         V4         V5
------------ ---------- ---------- ---------- ---------- ----------
--         1         18          5         12          8          0
--         2          0          8         -6          4         -2
--         3          2          8          9          3          4
--         4          1          9          0          5          7
--         5          7          6          2          9          7

*/

SELECT
A.v1 + B.v1 as v1,
A.v2 + B.v2 as v2,
A.v3 + B.v3 as v3,
A.v4 + B.v4 as v4,
A.v5 + B.v5 as v5
FROM TESTMAT_A a INNER JOIN TESTMAT_B b ON A.nr = B.nr;

--        V1         V2         V3         V4         V5
------------ ---------- ---------- ---------- ----------
--        15         10         10         16          6
--         3         16          0          8          7
--         4         16         18          6          8
--         2          5          4         10          6
--        14         12          4          0          7

--=====================================

--SUBTRACTION A-B
SELECT
A.v1 -  B.v1 as v1,
A.v2 - B.v2 as v2,
A.v3 - B.v3 as v3,
A.v4 - B.v4 as v4,
A.v5 - B.v5 as v5
FROM TESTMAT_A a INNER JOIN TESTMAT_B b ON A.nr = B.nr;

--        V1         V2         V3         V4         V5
------------ ---------- ---------- ---------- ----------
--       -21          0        -14          0          6
--         3          0         12          0         11
--         0          0          0          0          0
--         0        -13          4          0         -8
--         0          0          0        -18         -7

DROP TABLE TESTMAT_A;

--===============================================

CREATE TABLE TESTMAT_A(
nr INTEGER NOT NULL,
v1 INTEGER NOT NULL,
v2 INTEGER NOT NULL,
v3 INTEGER NOT NULL,
v4 INTEGER NOT NULL,
v5 INTEGER NOT NULL,
v6 INTEGER NOT NULL,
v7 INTEGER NOT NULL,
v8 INTEGER NOT NULL
);

INSERT INTO TESTMAT_A VALUES(1, -3, 5, -2, 8, 6, 8, 9, 0);
INSERT INTO TESTMAT_A VALUES(2, 3, 8, 6, 4, 9, 3, 6, -7);
INSERT INTO TESTMAT_A VALUES(3, 2, 8, 9, 3, 4, 3, 7, 9);
INSERT INTO TESTMAT_A VALUES(4, 1, -4, 4, 5, 7, 9, 9, 5);
INSERT INTO TESTMAT_A VALUES(5, 7, 6, 2, -9, 7, 9, 9, 6);
INSERT INTO TESTMAT_A VALUES(6, 5, 2, 9, 6, 5, 8, 6, 8);
INSERT INTO TESTMAT_A VALUES(7, 4, 0, 2, 5, 9, 5, 8, 3);

--==============================================

DROP TABLE TESTMAT_B;

CREATE TABLE TESTMAT_B(
nr INTEGER NOT NULL,
v1 INTEGER NOT NULL,
v2 INTEGER NOT NULL,
v3 INTEGER NOT NULL,
v4 INTEGER NOT NULL,
v5 INTEGER NOT NULL,
v6 INTEGER NOT NULL,
v7 INTEGER NOT NULL,
v8 INTEGER NOT NULL,
v9 INTEGER NOT NULL
);

INSERT INTO TESTMAT_B VALUES(1, 18, 5, 12, 8, 0, 8, 9, -2, 1);
INSERT INTO TESTMAT_B VALUES(2, 0, 8, -6, 4, -2, 3, 6, 5, 6);
INSERT INTO TESTMAT_B VALUES(3, 2 , 8, 9, 3, 4, 3, 7, 4, 9);
INSERT INTO TESTMAT_B VALUES(4, 1, 9, 0, 5, 7, 9, -9, 5, 3);
INSERT INTO TESTMAT_B VALUES(5, 7, 6, 2, 9, 7, 9, 9, 6, 8);
INSERT INTO TESTMAT_B VALUES(6, 5, 0, 9, 6, 5, 8, 6, 8, 9);
INSERT INTO TESTMAT_B VALUES(7, 0, 4, 2, 5, 9, 0, 8, 6, 1);
INSERT INTO TESTMAT_B VALUES(8, 5, 9, 5, 3, 7, -3, 0, 2, 6);

--===== Transform matrix A into array A ======
WITH arr_A AS
(SELECT nr, decode(mod(rownum,8),0,8,mod(rownum,8)) Col, Val FROM TESTMAT_A UNPIVOT (Val FOR Col IN (v1,v2,v3,v4,v5,v6,v7,v8)) unpvt ),
--===== Transform matrix B into array B ======
arr_B AS
(SELECT nr, decode(mod(rownum,9),0,9,mod(rownum,9)) Col, Val FROM TESTMAT_B UNPIVOT (Val FOR Col IN (v1,v2,v3,v4,v5,v6,v7,v8,v9)) unpvt),
--===== Calculate Product A*B =================
product AS (SELECT rowA as Rw, colB as Col, sum(product) Val FROM
(SELECT arr_A.nr rowA, arr_A.col colA,
arr_B.nr rowB, arr_B.col colB,
arr_A.val * arr_B.val as product
FROM arr_A INNER JOIN arr_B
ON arr_A.col = arr_B.nr) t1
GROUP BY colB, rowA
)
--===== Back to matrix form (reverse transformation) =======
SELECT * FROM product
PIVOT (max(Val) FOR Col IN (1,2,3,4,5,6,7,8,9)) piv
ORDER BY rw;

--RW	1	2	3	4	5	6	7	8	9
-----------------------------------------
--1	32	153	18	177	201	175	91	217	162
--2	113	178	64	202	119	228	228	178	180
--3	145	306	151	206	210	127	212	195	260
--4	150	173	210	206	269	173	157	197	204
--5	245	150	209	221	150	116	383	159	216
--6	229	293	263	252	259	212	207	212	282
--7	184	194	160	223	224	195	180	173	180
```

[Updated on: Mon, 08 February 2010 10:16]

Report message to a moderator

Re: matrix multiplication [message #442501 is a reply to message #442499] Mon, 08 February 2010 10:45
 Michel Cadot Messages: 65085Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
joshua82 wrote on Mon, 08 February 2010 17:11
Michel Cadot wrote on Mon, 08 February 2010 09:46
You have to first know how to order the rows in the tables.

The incremental number of row isn't an order?

sorry, sorry, sorry, didn't see it, time to have a snap.

You have the row numbers and the column numbers and the formulas, so you have the query. The only difficuly is to carefully write each result column formula.

Regards
Michel

Re: matrix multiplication [message #442502 is a reply to message #442500] Mon, 08 February 2010 10:46
 Michel Cadot Messages: 65085Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
PIVOT/UNPIVOT does not exist in 10g.

Regards
Michel

[Updated on: Mon, 08 February 2010 10:47]

Report message to a moderator

Re: matrix multiplication [message #442504 is a reply to message #442485] Mon, 08 February 2010 11:57
 Michel Cadot Messages: 65085Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
```CREATE TABLE A(
r NUMBER NOT NULL,
A NUMBER NOT NULL,
B NUMBER NOT NULL,
C NUMBER NOT NULL,
D NUMBER NOT NULL,
E NUMBER NOT NULL,
F NUMBER NOT NULL,
G NUMBER NOT NULL,
H NUMBER NOT NULL
);
INSERT INTO A VALUES(1, -3, 5, -2, 8, 6, 8, 9, 0);
INSERT INTO A VALUES(2, 3, 8, 6, 4, 9, 3, 6, -7);
INSERT INTO A VALUES(3, 2, 8, 9, 3, 4, 3, 7, 9);
INSERT INTO A VALUES(4, 1, -4, 4, 5, 7, 9, 9, 5);
INSERT INTO A VALUES(5, 7, 6, 2, -9, 7, 9, 9, 6);
INSERT INTO A VALUES(6, 5, 2, 9, 6, 5, 8, 6, 8);
INSERT INTO A VALUES(7, 4, 0, 2, 5, 9, 5, 8, 3);
--==============================================

CREATE TABLE B(
r NUMBER NOT NULL,
A NUMBER NOT NULL,
B NUMBER NOT NULL,
C NUMBER NOT NULL,
D NUMBER NOT NULL,
E NUMBER NOT NULL,
F NUMBER NOT NULL,
G NUMBER NOT NULL,
H NUMBER NOT NULL,
I NUMBER NOT NULL
);
INSERT INTO B VALUES(1, 18, 5, 12, 8, 0, 8, 9, -2, 1);
INSERT INTO B VALUES(2, 0, 8, -6, 4, -2, 3, 6, 5, 6);
INSERT INTO B VALUES(3, 2 , 8, 9, 3, 4, 3, 7, 4, 9);
INSERT INTO B VALUES(4, 1, 9, 0, 5, 7, 9, -9, 5, 3);
INSERT INTO B VALUES(5, 7, 6, 2, 9, 7, 9, 9, 6, 8);
INSERT INTO B VALUES(6, 5, 0, 9, 6, 5, 8, 6, 8, 9);
INSERT INTO B VALUES(7, 0, 4, 2, 5, 9, 0, 8, 6, 1);
INSERT INTO B VALUES(8, 5, 9, 5, 3, 7, -3, 0, 2, 6);```

Next time, please post a WORKING test case.

```SQL> with
2    a2 as (
3      select r, col, decode(col, 1,a, 2,b, 3,c, 4,d, 5,e, 6,f, 7,g, 8,h) val
4      from a, (select level col from dual connect by level <= 8)
5    ),
6    b2 as (
7      select r, col, decode(col, 1,a, 2,b, 3,c, 4,d, 5,e, 6,f, 7,g, 8,h, 9,i) val
8      from b, (select level col from dual connect by level <= 9)
9    ),
10    p as ( -- product
11     select a2.r, b2.col, sum(a2.val*b2.val) val
12     from a2, b2
13     where a2.col = b2.r
14     group by a2.r, b2.col
15   )
16  select r,
17         sum(decode(col,1,val)) c1,
18         sum(decode(col,2,val)) c2,
19         sum(decode(col,3,val)) c3,
20         sum(decode(col,4,val)) c4,
21         sum(decode(col,5,val)) c5,
22         sum(decode(col,6,val)) c6,
23         sum(decode(col,7,val)) c7,
24         sum(decode(col,8,val)) c8,
25         sum(decode(col,9,val)) c9
26  from p
27  group by r
28  order by r
29  /
R         C1         C2         C3         C4         C5         C6         C7         C8         C9
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1         32        153         18        177        201        175         91        217        162
2        113        178         64        202        119        228        228        178        180
3        145        306        151        206        210        127        212        195        260
4        150        173        210        206        269        173        157        197        204
5        245        150        209        221        150        116        383        159        216
6        229        293        263        252        259        212        207        212        282
7        184        194        160        223        224        195        180        173        180

7 rows selected.```

Regards
Michel

[Updated on: Mon, 08 February 2010 11:59]

Report message to a moderator

Re: matrix multiplication [message #442552 is a reply to message #442485] Mon, 08 February 2010 23:30
 rleishman Messages: 3727Registered: October 2005 Location: Melbourne, Australia Senior Member
joshua82 wrote on Tue, 09 February 2010 00:55
I found a solution in SQL (here), but I need to do that in PL/SQL.

Sounds like a homework assignment.
Re: matrix multiplication [message #442553 is a reply to message #442552] Mon, 08 February 2010 23:32
 BlackSwan Messages: 25531Registered: January 2009 Location: SoCal Senior Member
>Sounds like a homework assignment.
REALLY?

who else would be attempting matrix math while (ab)using any RDBMS?
Re: matrix multiplication [message #442584 is a reply to message #442553] Tue, 09 February 2010 03:58
 joshua82 Messages: 31Registered: December 2009 Member
BlackSwan wrote on Mon, 08 February 2010 23:32
who else would be attempting matrix math while (ab)using any RDBMS?

this sounds good..but how? (I'm a real newbie with Oracle.. )

I solved with Michel solution (thanks!!) but I'm in trouble to create subquery like a2: in a real test I'm in a situation like this (a column vector represent a matrix):
```CREATE TABLE example(
value NUMBER
);
INSERT INTO example VALUES(11);
INSERT INTO example VALUES(12);
INSERT INTO example VALUES(13);
INSERT INTO example VALUES(21);
INSERT INTO example VALUES(22);
INSERT INTO example VALUES(23);
INSERT INTO example VALUES(31);
INSERT INTO example VALUES(32);
INSERT INTO example VALUES(33);

-------which is like:-----------
VALUE
11
12
13
21
22
23
31
32
33

--- which represents a matrix:
11         12      13
21         22      23
31         32      33

```

but, in your solution, I need to create a situation like this:
```r     col   val
1	1	11
2	1	21
3	1	31
1	2	12
2	2	22
3	2	32
1	3	13
2	3	23
3	3	23
```

Re: matrix multiplication [message #442598 is a reply to message #442502] Tue, 09 February 2010 05:38
Michel Cadot wrote on Mon, 08 February 2010 22:16
PIVOT/UNPIVOT does not exist in 10g.

Regards
Michel

Quote:
Here is the example in a version for ORACLE 11.
The code is very near to the original, guess there are more effective ways to solve the problem:

sriram
Re: matrix multiplication [message #442602 is a reply to message #442598] Tue, 09 February 2010 06:09
 Michel Cadot Messages: 65085Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
OP is clear on his version, try to FIRST answer his question, otherwise there is no limit to what can be posted, you could also post a solution to the next topic.

Regards
Michel
Re: matrix multiplication [message #442603 is a reply to message #442584] Tue, 09 February 2010 06:10
 Michel Cadot Messages: 65085Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Quote:
but, in your solution, I need to create a situation like this:

You do not need, the query does it for you from the input table. You just have to put the correct numbers in "connect by" clause.

Regards
Michel
Re: matrix multiplication [message #442613 is a reply to message #442603] Tue, 09 February 2010 07:33
 joshua82 Messages: 31Registered: December 2009 Member
Michel Cadot wrote on Tue, 09 February 2010 06:10
You just have to put the correct numbers in "connect by" clause.

This makes an error (a matrix of 27 rows, not a matrix of 9 rows )..

The problem is about "r" and "col" column which values is really important for your solution. I came from a vector matrix and I need to put in "r" and "col" the right values (as it can be considered as a matrix 3 x 3)
```r     col   val
1	1	11
2	1	21
3	1	31
1	2	12
2	2	22
3	2	32
1	3	13
2	3	23
3	3	23```

Thanks!

[Updated on: Tue, 09 February 2010 07:33]

Report message to a moderator

Re: matrix multiplication [message #442626 is a reply to message #442613] Tue, 09 February 2010 09:41
 Michel Cadot Messages: 65085Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Quote:
I need to put in "r" and "col" the right values (as it can be considered as a matrix 3 x 3)

No, you don't need, you need... (what is in my previous post).
Just create A and B table for your data, update the query with correct numbers and execute the query.

Regards
Michel
Re: matrix multiplication [message #442628 is a reply to message #442626] Tue, 09 February 2010 09:53
 joshua82 Messages: 31Registered: December 2009 Member
the problem is that I can't create A and B.
I have a table (like example in my previous post) which is created in another procedure.

example is like this:
```VALUE
11
12
13
21
22
23
31
32
33
```

I need to format this table like (and use it in your solution):
```r     col    val
1	1	11
2	1	21
3	1	31
1	2	12
2	2	22
3	2	32
1	3	13
2	3	23
3	3	33```

Thanks

[Updated on: Tue, 09 February 2010 09:56]

Report message to a moderator

Re: matrix multiplication [message #442630 is a reply to message #442628] Tue, 09 February 2010 09:59
 Michel Cadot Messages: 65085Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Quote:
I need to format this table like (and use it in your solution):

You don't need it for my solution, my solution starts from the tables you provided.

Quote:
example is like this:

As I said in my first answer:
Quote:
You have to first know how to order the rows in the tables.
Which one is the first one? and so on.
If you have no column to order the rows then it is not possible.

Regards
Michel
Re: matrix multiplication [message #442632 is a reply to message #442485] Tue, 09 February 2010 10:14
 joshua82 Messages: 31Registered: December 2009 Member
I understand.
So, I can pre-process my table example and I can create a matrix 3x3, like this:

```row     A          B          C
1      11         12       13
2      21         22       23
3      31         32       33
```

But my question is the same: how extract a 3x3 matrix from a vector matrix?

thanks
Re: matrix multiplication [message #442636 is a reply to message #442632] Tue, 09 February 2010 10:59
 Michel Cadot Messages: 65085Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Quote:
how extract a 3x3 matrix from a vector matrix?

I don't understand the question.
What should be the result from the matrix you gave?

Regards
Michel
Re: matrix multiplication [message #442656 is a reply to message #442636] Tue, 09 February 2010 14:44
 joshua82 Messages: 31Registered: December 2009 Member
Michel Cadot wrote on Tue, 09 February 2010 10:59
Quote:
how extract a 3x3 matrix from a vector matrix?

I don't understand the question.

No, it's me. I'm not able to explain what I need.

The problem is that in input I've a matrix column (like example, not like A or B) which came from a previous procedure; this matrix column it's a table representation of a 3x3 matrix. Like this:

```----the matrix column 'example' in input
VALUE
11
12
13
21
22
23
31
32
33
```

and

```---what the 'example' table represent:
11         12       13
21         22       23
31         32       33
```

I need from example to replace values in this form:

```r     col    val
1	1	11
2	1	21
3	1	31
1	2	12
2	2	22
3	2	32
1	3	13
2	3	23
3	3	33```

and then use it in your solution.

Re: matrix multiplication [message #442679 is a reply to message #442656] Tue, 09 February 2010 23:17
 Michel Cadot Messages: 65085Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Once again in the input table you must have a column that indicates the order.
And you need a rule to transform the subsequent rows into columns:
- is this, row1-col1, row1-col2, row1-col3, row2-col1...
- is this, row1-col1, row2-col1, row3-col1, row1-col2...
- other?

Regards
Michel
Re: matrix multiplication [message #442701 is a reply to message #442679] Wed, 10 February 2010 00:40
 Michel Cadot Messages: 65085Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
```create table t (id integer, val integer);
insert into t values (1,  11);
insert into t values (2,  12);
insert into t values (3,  13);
insert into t values (4,  21);
insert into t values (5,  22);
insert into t values (6,  23);
insert into t values (7,  31);
insert into t values (8,  32);
insert into t values (9,  33);
commit;```

```SQL> select * from t order by id;
ID        VAL
---------- ----------
1         11
2         12
3         13
4         21
5         22
6         23
7         31
8         32
9         33

9 rows selected.```

For the first case of sharing out:
```SQL> with
2    data as (
3      select trunc((id-1)/3)+1 line, mod(id-1,3)+1 col, val
4      from t
5    )
6  select line,
7         max(decode(col,1,val)) col1,
8         max(decode(col,2,val)) col2,
9         max(decode(col,3,val)) col3
10  from data
11  group by line
12  order by line
13  /
LINE       COL1       COL2       COL3
---------- ---------- ---------- ----------
1         11         12         13
2         21         22         23
3         31         32         33

3 rows selected.```

For the second case, just swap line and column:
```SQL> with
2    data as (
3      select mod(id-1,3)+1 line, trunc((id-1)/3)+1 col, val
4      from t
5    )
6  select line,
7         max(decode(col,1,val)) col1,
8         max(decode(col,2,val)) col2,
9         max(decode(col,3,val)) col3
10  from data
11  group by line
12  order by line
13  /
LINE       COL1       COL2       COL3
---------- ---------- ---------- ----------
1         11         21         31
2         12         22         32
3         13         23         33

3 rows selected.```

Regards
Michel

Re: matrix multiplication [message #442715 is a reply to message #442485] Wed, 10 February 2010 02:23
 joshua82 Messages: 31Registered: December 2009 Member
Thanks, Michel!
 Previous Topic: Loading data from flat file using ORGANIZATION EXTERNAL Next Topic: ORA-06508: PL/SQL: could not find program unit being called
Goto Forum:

Current Time: Sun Jul 23 13:56:59 CDT 2017

Total time taken to generate the page: 0.13813 seconds