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!

To tell the truth I dont understand the question.

What is the meaning of your tables? What is variable? What is ID? is that a unique key for one of tables? Maybe for both?
As I understood from your question in second table if, say, variable = 3, then the response field womehow relates to the value of variable3 in Table1? Can't you write
select ID, Response from TABLE2 where variable in (2, 3, 5) instead of

"SELECT ID, Response FROM Table2 WHERE variable = 2

SELECT ID, Response FROM Table2 WHERE variable = 3

SELECT ID, Response FROM Table2 WHERE variable =5"?

And, are you considering any table schema reorganization? Because if the way I understood your table schemas is right, then you should consider some other way to store data-storing variable number in one table as a column name and as a value in the other seems rather illogical to me.