Exporting Normalized Table to Matrix format in MySQL

To give some background, I am modeling how different home attributes affect the prices of the homes around them. To do this, I have a table of 4,000 home sales and the demographics of each home. I'm generating a value as a measure of "substitutability" between each pair of homes.

My first thought in creating this was to build a 4000x4000 matrix in Excel. However, once my calculations got a little hairy, I bumped up against memory limits pretty quickly.

So now I've put the transactions into the above mentioned table and I want to build an "interaction" table, with each row being a house pair with one column as the substitutability measure.

The table structure would look something like:

House1ID   House2ID    SubIndex
1          2           400
1          3           450
2          3           500

My problem is that the statistical software package I'm using wants the data in the following forma开发者_运维技巧t:

    1      2      3
1   0     400    450
2  400     0     500
3  450    500     0

Is there a way to go from the table structure mentioned above to the matrix output needed?

All help is greatly appreciated!

EDIT: After a little more investigation, I've realized I can at generate the normalized "table" now with a View using a Cross Join. While this doesn't really change the question at all, I thought it interesting and figured I would point out the method I was using.

I would look into using a view to have the data presented in the format you need.

There are some great examples and hints on using views here.