HOW to group into one report, without running 10 times to get all 1

[mGis]

How do I produce the report were columns are group into Ans_String_Val for specific Ans_Quest

tblAnswers
Ans_QuestProdID = Relates to Product
Ans_Quest = Relates to Question Number
Ans_String_Val = Is the answer to the Question
Ans_UserID = User ID

If Ans_QuestID=201 relates to Soda and
there are ten questions reguarding soda. I want to produce the following report :

Ans_UserID Ans_Quest=1 , ... Ans_Quest=10
111 'Ans_Sting_Val 'Ans_String_Val
for Quest=1' for quest=10

112 'Ans_Sting_Val 'Ans_String_Val
for Quest=1' for quest=10'

113 'Ans_Sting_Val 'Ans_String_Val
for Quest=1 for quest=10'

SELECT Ans_AnsweredByUserID, Ans_String_Val
FROM tblAnswers
WHERE Ans_QuestID=201 and
Ans_Quest=1; WILL give me one column, but HOW to group into one report, without running 10 times to get all.
Thanks any help is greatly appreciated.
Michael

 

[MikeJones]

The only way I know to do this is to join the same table in as many times as needed. for example...

SELECT t1.Ans_AnsweredByUserID,
t1.Ans_String_Val,
t2.Ans_String_Val
FROM tblAnswers t1, tblanswers t2
WHERE t1.Ans_QuestID=201
and t1.Ans_QuestID = t2.Ans_QuestID
and t1.Ans_UserID = t2.Ans_UserID
and t1.Ans_QuestProdID = t2.Ans_QuestProdID
and Ans_Quest=1;

HTH,

Mike.

 

[mGis]

I tried the query that you suggested and kept on getting the ERROR MESSAGE - 'Ans_String_Val already exists in table 'Results'

So I declared each table with 'as Ans_n '

as in below:

select t1.Ans_AnsweredByUserID,
t1.Ans_String_Val as Ans_1,
t2.Ans_String_Val as Ans_2,
t3.Ans_String_Val as Ans_3,
t4.Ans_String_Val as Ans_4,
t5.Ans_String_Val as Ans_5,
t6.Ans_String_Val as Ans_6,
t7.Ans_String_Val as Ans_7
from tblAnswers t1, tblAnswers t2, tblAnswers t3, tblAnswers t4, tblAnswers t5,
tblAnswers t6, tblAnswers t7
where
t1.Ans_QuestProdID=112 and
t1.Ans_QuestProdID=t2.Ans_QuestProdID and
t1.Ans_QuestProdID=t3.Ans_QuestProdID and
t1.Ans_QuestProdID=t4.Ans_QuestProdID and
t1.Ans_QuestProdID=t5.Ans_QuestProdID and
t1.Ans_QuestProdID=t6.Ans_QuestProdID and
t1.Ans_QuestProdID=t7.Ans_QuestProdID and

t1.Ans_AnsweredByUserID=15046 and
t1.Ans_AnsweredByUserID=t2.Ans_AnsweredByUserID and
t1.Ans_AnsweredByUserID=t3.Ans_AnsweredByUserID and
t1.Ans_AnsweredByUserID=t4.Ans_AnsweredByUserID and
t1.Ans_AnsweredByUserID=t5.Ans_AnsweredByUserID and
t1.Ans_AnsweredByUserID=t6.Ans_AnsweredByUserID and
t1.Ans_AnsweredByUserID=t7.Ans_AnsweredByUserID and


t1.Ans_Quest=1 and
t2.Ans_Quest=2 and
t3.Ans_Quest=3 and
t4.Ans_Quest=4 and
t5.Ans_Quest=5 and
t6.Ans_Quest=6 and
t7.Ans_Quest=7;

It works perfectly,
Thanks Michael
P.S.
My only concern is that it is database of 20,000 users and growing by about 900-1000 users a day.
Does anyone know a better(more efficient)way for this query

 

[MikeJones]

Erm yes, you do end up with quite long queries don't you!!

I'm afraid that I know of no ANSI standard SQL statement that can resolve this. Therefore any solution will be RDBMS specific.

I've heard rumors that some DB's offer facilities that can do this, however another person is suffering a similar problem, wanting variable width result sets based on the dataset in question. I posted an Oracle solution to their problem in thread220-66590 yours will be quite similar but would have to return as many Ans_String_Val as neccesay concatonated together.

If an Oracle solution will do then I can post a more tailored solution. If you on something else (maybe MS) then perhaps someone else can point you in the right direction.

HTH,

Mike.