n Thu, 28 Jun 2007 12:44:17 -0700, nina297 <nina.childress@ssa.gov>
wrote:

With a WHERE clause test?  Or perhaps I do not understand the
question.

SELECT topic, question, answer
 FROM Topics AS A
 JOIN QuesNans AS B
   ON A.topicid = B.topicid
WHERE A.topic = 'Topic One'
ORDER BY a.topic

By the way, it is common to make table names plural, and column names
singular.  And it is much preferred to use the SQL-92 join syntax and
leave the WHERE clause for the rest tests to include/exclude rows (at
least in so far as they do not break OUTER joins.)

Roy Harvey
Beacon Falls, CT

So you get something like:

  Topic       Q1      A1    Q2   A2  ....
  T Four      Quest1  Ans1  
  T One       Quest1  Ans2  Quest2 Ans2
  ....

If you know the maximum number of question per topics, you can do:

  SELECT A.Topic,
         Q1 = MIN (CASE n.n WHEN 1 THEN B.Questions END),
         A1 = MIN (CASE n.n WHEN 1 THEN B.Answers END),
         Q2 = MIN (CASE n.n WHEN 2 THEN B.Questions END),
         A2 = MIN (CASE n.n WHEN 2 THEN B.Answers END),
         ...
         Q5 = MIN (CASE n WHEN 5 THEN B.Questions END),
         A5 = MIN (CASE n WHEN 5 THEN B.Answers END)
  FROM   Topics AS A
  JOIN   QuesNans AS B ON A.topicid = B.topicid
  CROSS  JOIN (SELECT 1 AS n UNION ALL
               SELECT 2 UNION ALL
               SELECT 3 UNION ALL
               SELECT 4 UNION ALL
               SELECT 5) AS n
  GROUP BY A.Topic
   
This is a crosstab query. There are two "tricks". The first is the
derived table that generates the numbers 1 to 5. This is a query within
the query, which is a very useful technique, because the optimizer is
very good at recasting computation order for better performance. The
other is the MIN(CASE. The MIN here serves to get all one row, but the
MIN only sees one value. In fact MAX would work just as well.

If you cannot assume the maxmim number of questions per topic, you
need to build the query dynamically, which is quite an increase in
complexity. The third party tool RAC, at www4sql.rac.net is popular
for this.