CODEWITHSUNDEEP
sqlsql-server
vap0991
vap0991

Reputation: 113

How can I get the matrix for these tables?

I have two tables here and need to produce a matrix for all combinations

Table 1

Brand Company ID
1     1       1 
2     2       2
3     3       3

Table 2

Prod1 Prod2 Prod3 Prod4 Prod5 
4     5     6     18    19
5     6     7     20    5

The result I'm trying to achieve

Result table: 

Brand Company ID Prod1 Prod2 Prod3 Prod4 Prod5 
1     1       1   4     5     6     18    19
1     1       1   5     6     7     20    5
2     2       2   4     5     6     18    19
2     2       2   5     6     7     20    5

I could have worked with this if they have some kind of ID just not to how to approach this to get the matrix.

Thank you

Upvotes: 1

Views: 29

Answers (1)

SqlZim
SqlZim

Reputation: 38063

Not sure what happened to the third row from table1 in your query and why it isn't in the result, but I think you are looking for a cross join.

select Brand, Company, ID, Prod1, Prod2, Prod3, Prod4, Prod5 
from table1
  cross join table2

rextester demo: http://rextester.com/UOZ33372

returns (with added order by):

+-------+---------+----+-------+-------+-------+-------+-------+
| Brand | Company | ID | Prod1 | Prod2 | Prod3 | Prod4 | Prod5 |
+-------+---------+----+-------+-------+-------+-------+-------+
|     1 |       1 |  1 |     4 |     5 |     6 |    18 |    19 |
|     1 |       1 |  1 |     5 |     6 |     7 |    20 |     5 |
|     2 |       2 |  2 |     4 |     5 |     6 |    18 |    19 |
|     2 |       2 |  2 |     5 |     6 |     7 |    20 |     5 |
|     3 |       3 |  3 |     4 |     5 |     6 |    18 |    19 |
|     3 |       3 |  3 |     5 |     6 |     7 |    20 |     5 |
+-------+---------+----+-------+-------+-------+-------+-------+

Upvotes: 2

Related Questions