Re: question about a query
- From: nina297 <nina.childress@xxxxxxx>
- Date: Fri, 29 Jun 2007 06:00:31 -0700
On Jun 28, 4:32 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
nina297 (nina.childr...@xxxxxxx) 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...@xxxxxxxxxxxxx
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.
.
- References:
- question about a query
- From: nina297
- Re: question about a query
- From: Erland Sommarskog
- question about a query
- Prev by Date: Re: Output Column names in each Row along with the row Value
- Next by Date: Re: DELETE where syntax ... need help :)
- Previous by thread: Re: question about a query
- Next by thread: Re: XML INTO SQL
- Index(es):
Relevant Pages
|
