How to avoid multiple joins on the same table (it has 200 columns to hold IDs. Another table has a mapping of IDs to values) I have a table Checklist with the following columns

ChkId, Q1_ID, Comment1, Checked1, Q2_ID, Comment2, Checked2, ...
Q200_ID...

I have a table Questions with the following columns

Q_ID, Prompt

How do I replace Checklist.Q#_ID with the matching Questions.Prompt without joining them up to 200 times? Is there an elegant solution?
Take a look at my answer here to understand why your original design is a very bad idea from a relational database point of view.

Basically, you have "squashed" two entities into one! Your scenario is very similar to that question! You need to normalise your data - this will greatly assist your query formulation and speed!

It appears that there are two entities here (IMHO) a checklist entity and a question entity.

Checklists are identified by a checklist_id (chk_id) and questions are uniquely identifed by a checklist_id and a question_id. Perhaps "quiz" might be a better name? You could have history quizzes, sports quizzes &amp;c. with different chk_id's.

So, in order to tackle the underlying problem, you should create a question table (see SQLFiddle here):

CREATE TABLE question
(
  chk_id INTEGER NOT NULL,
  q_id INTEGER NOT NULL,
  q_comment VARCHAR (255) NOT NULL,
  q_checked BIT NOT NULL,
  CONSTRAINT question_pk PRIMARY KEY (chk_id, q_id)
);


You could implement my strategy in VIEWs if you can't change the underlying table structure - that will be a bit clumsy, but still better than your current scenario!

Sample checklist table:

A sample of the first 3 questions extracted from your checklist table and sample data:

CREATE TABLE checklist -- you have this already!
(
  chk_id INTEGER PRIMARY KEY,
  q1_id  INTEGER NOT NULL,
  q1_comment VARCHAR (255) NOT NULL, -- or whatever size suits.
  q1_checked BIT NOT NULL,

  q2_id INTEGER NOT NULL,
  q2_comment VARCHAR (255),
  q2_checked BIT NOT NULL,

  q3_id INTEGER NOT NULL,
  q3_comment VARCHAR (255),
  q3_checked BIT NOT NULL

  -- &amp;c. 197 times.
);

INSERT INTO checklist
VALUES
(233, 1, 'Good question!', 1, 2, 'Crap', 0, 3, 'Better', 1),
(234, 1, 'An OK question', 0,  2, 'Bleuch...', 1,  3, 'Brilliant!!', 0);


If the "comment" field is the question itself - i.e. "prompt", substitute text of your choice - my comments are opinions about the question... not really sure. Easily modifiable!

You will then need to construct a series of 200 INSERTs like this:

INSERT INTO question
SELECT 
  chk_id, 
  q1_id, 
  q1_comment, 
  q1_checked 
FROM checklist;


You will have to do this 200 times (q2... q200). Obviously, this will require some work (either manual typing or scripting) to get your data out of your original entity, but it will be worth it in the long run!

Here are the three question entries (by two checklists - history or sport):

SELECT * FROM question;


Result:

chk_id  q_id          q_comment     q_checked
   233     1     Good question!          true
   233     2               Crap         false
   233     3             Better          true
   234     1     An OK question         false
   234     2          Bleuch...          true
   234     3        Brilliant!!         false


Your tables will be much more performant and much easier to manipulate!