Posted by nina297 on 06/29/07 13:00

On Jun 28, 4:32 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> nina297 (nina.childr...@ssa.gov) writes:
> > I've written this query:
>
> > select distinct topics, questions, answer
> > from topics AS A, QuesNans AS B
> > where A.topicid = B.topicid
> > order by a.topics
>
> > The results are:
> > Topic Questions Answers
> > Topic Four Question 1 Answer to question 1
> > Topic One Quesstion 2 Answer to question 2
> > Topic One Question 1 Answer to question 1
> > Topic Three Question 1 Answer to question 1
> > Topic Two Question 2 Answer to question 2
>
> > How do I get one topic listed but all of the questions that go with
> > that topic?
>
> 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.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -

Thanks so much for your help. I will try both options.

[Back to original message]