Re: question about a query

nina297 (nina.childress@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, esquel@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Error using a derived table
    ... to re-write your query without all of the nested SELECTs and the CASE ... > union all ... > select s.misspelledname as 'Resortname' ... I am using SQL Server 2000 EE. ...
    (microsoft.public.sqlserver.programming)
  • Re: Indexes being improperly used when selecting data through a view
    ... I run the same query against the table directly and it looks ... What happens if you actually have the UNION ALL, ... With UNION SQL Server will have to do an operation ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... in the query, prefix all your columns with aliases (or the table ... of the procedure to avoid duplicates it seems to have thrown off the ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: question about a query
    ... JOIN QuesNans AS B ON A.topicid = B.topicid ... CROSS JOIN (SELECT 1 AS n UNION ALL ... This is a crosstab query. ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)
  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)