|
|
Posted by Erland Sommarskog on 06/28/07 20:32
nina297 (nina.childress@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|