Help With Flattening A Table

Hi All,

I have a database that stores some data from two questionnaires. Table One has been created in a fairly straightforward manner. It has the following fields:

ID, variable1, variable2, variable3, ..., variablen

Table Two is a bit more complicated. I'm guessing whoever created it wanted to do things a bit more "efficiently" and it has the following fields.

ID, variable, response.



If I wanted to, say, extract variables 2, 3 and 5 from Table One I could write:

SELECT ID, variable2, variable3, variable 5 FROM TableOne

I don't know how to do a similar thing with Table 2 using only one statement. ie, I can only think of doing:

SELECT ID, Response FROM Table2 WHERE variable = 2

SELECT ID, Response FROM Table2 WHERE variable = 3

SELECT ID, Response FROM Table2 WHERE variable = 5



This isn't much good to me as I need the data from Table Two in a single table. Once I've done that, I need to join it to Table One. Ordinarily I'd know how to do that, but I don't know how to do given the format of Table Two.

So... I was wondering if anyone could offer advice on how to "flatten" Table Two?



I would like to be able to do something like:

SELECT t1.ID, t1.variable1, t1.variable2, t2.variable6, t2.variable7

FROM TableOne t1 JOIN TableTwo t2 ON t1.ID = t2.ID



Any help would be much appreciated!