Join rows of table A with columns of table B in MySQL
<p>I have two tables, A and B. </p>

<p>Table A contains a list of samples, which are numbered, but also have character id. It has the form</p>

<pre>
sample_num    sample_id  
1              id_1   
2              id_2
.              .
.              .
.              .
n              id_n
</pre>

<p>Table B contains gene expression data for each sample. It has the form</p>

<pre>
probe_num     1   2 . . . n    
1            
2
.
.
.
m
</pre>

<p>Ideally I would have samples as rows, and the genes as columns, however, there are too many genes for MySQL to store as columns so I cannot really change this.</p>

<p>Now, I am given a subset of sample id's and asked to return the associated gene expression data. But I can't figure out how to perform the required join. I need to join on the column <em>identifiers</em> from table B with the sample_num column from table A.  </p>

<p>This would be easy if I could, for instance, transpose table B, but I don't know if this is possible or not given the limitation on the number of columns. </p>