Hi,
i think this would give you some idea how to do it
this is with oracle 9 similarly you can do it by
writing a SP in Sql server using similar construct
create or replace function FN_GETRESULTS() return
varchar2 as
lc_str VARCHAR2(4000);
lc_Question VARCHAR2(4000);
lc_Answer VARCHAR2(4000);
c_dummy My_package.RPT_RESULTS;--
begin
OPEN c_dummy FOR
select qmast.QuestionDescription,
case when (ansmast.AnswerId!=null) then
ansmast.AnswerDescription
else 'Poor' end
AnswerDescription
from Results rslt
inner join QuestionsMaster qmast on(
qmast.QuestionID=rslt.QuestionID)
left join AnswersMaster ansmast on
(ansmast.AnswerId=rslt.Answerid)
LOOP
FETCH c_dummy INTO lc_colval;
EXIT WHEN c_dummy%NOTFOUND;
lc_str := lc_str || ', ' || lc_colval ||
lc_Question;
END LOOP;
CLOSE c_dummy;
RETURN SUBSTR(lc_str,2);
end FN_GETRESULTS;
--- "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
wrote:
>
> Hi all,
>
> This is really a SQL question but maybe someone can
> help me. I am creating a report that will be run
> from my asp.net page. The report is for the results
> of an employee eavaluation. So there are 3 tables
> involved. Here is the basic structure of the 3
> tables:
>
> Results table:
>
> ResultsID
> QuestionID
> AnswerID
>
> Questions Master table:
>
> QuestionID
> QuestionDescription
>
> Answers Master table:
>
> AnswerID
> AnswerDescription
>
> There are several question master rows. There are 5
> answer master rows with these values:
>
> 1 Excellent
> 2 Very Good
> 3 Satisfactory
> 4 Needs Improvement
> 5 Poor
>
> As you can see the results table conatins a row for
> each question and the selected answer. But on my
> report I want to show the selected answer as well as
> the answer descriotion for the unselected answers
> horizontally across the page. So I want to create a
> "Flat" file where all my answer descriptions are in
> the same row. How can I do this in SQL? I hope
> this makes sense.
>
> Thanks,
> Bob
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~-->
> Make a clean sweep of pop-up ads. Yahoo! Companion
> Toolbar.
> Now with Pop-Up Blocker. Get it for free!
>
http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/saFolB/TM
>
--------------------------------------------------------------------~->
>
>
>
> Yahoo! Groups Links
>
> http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/
>
>
> [EMAIL PROTECTED]
>
>
>
>
________________________________________________________________________
Yahoo! India Matrimony: Find your life partner online
Go to: http://yahoo.shaadi.com/india-matrimony
------------------------ Yahoo! Groups Sponsor --------------------~-->
Make a clean sweep of pop-up ads. Yahoo! Companion Toolbar.
Now with Pop-Up Blocker. Get it for free!
http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/saFolB/TM
--------------------------------------------------------------------~->
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
