Populating Temp Table

[SteveMac32]

Hi all hope you can help as this has been bugging me for days.

I need to create a temporary table to then report on, the table column headers need to be the values from one table and the column content from another… it goes a bit like this.

I have a table that has among other things a QuestionAsked column and a QuestionId column. (the text in QuestionAsked can change which is why I cannot create a normal table with the headers as we will not know what they are.)

I have another table with AnswersGiven column and a QuestionId column, being the link between the two tables.

Need to populate the column headers of a temp table with the QuestionAsked and the values for each Question need to be corresponding AnswersGiven

 

[dGrouse]

I'm going to assume that this temporary table is a regular table for temporary use which you can assemble more-or-less manually. If you need a single, automated, one-fell-swoop query, it gets a little trickier (though it ain't impossible, of course).

Call your question lookup table QUESTION, and your answer table ANSWER:

QUESTION
(Q_ID INT
,Q_TEXT VARCHAR(50))

ANSWER
(A_ID INT
,Q_ID INT
,A_TEXT VARCHAR(500))

Notice that I'm assuming that you have textual answers that don't exceed 500 characters in length, along with a primary key of A_ID on your answer table. I also assume each answer is for one question.

Step 1: Make a shell of the table you want, which we'll call QAR (for "Question Answer Report"):

create table QAR
(A_ID int not NULL)

Step 2: Write a query to make SQL "alter table" statements to add a column for each of your question answers. Send the output to text, or a file, so you can copy it cleanly (in the next step):

select 'alter table qar add [' + a_text + '] varchar(500) null' from QUESTION

Step 3: run the batch of SQL you just created; you should have one row, and thus one new column, per question.

Step 4: write a query to make INSERT statements, using the appropriate column, for each of your answers. Again, you'll want to send the output to text, or a file.

select 
  'insert into qar (a_id, [' + q.q_text + ']) values ('a_id + ','' + a.a_text + ''')'
from
  answer a inner join question q
  on a.q_id = q.q_id

Step 5: run the hunk-o-code you just created in step 4.

If you don't care to track which ANSWER record was the source of each record in QAR, then leave off the A_ID column in QAR and elsewhere.

I think all of that will get you what you need. Again, if you need to wrap all this up into one big push-button thing, a bit more work is required.

Note that the above code was "lightly" tested only.

Jeff Prenevost
BI Consultant
Ann Arbor, MI

 

[SteveMac32]

Thanks dGrouse, I will work through this and get back to you.

I do need it as a one big button push, will that be much more work or can what you have given me be altered.

Thanks