query??

[ricksql@yahoo.com]

#temptable (studentID,p/f,examid,examna me):
1 p
2 f 100 bio
2 f 101 mTH
2 f 102 CHM
2 f 103 spts
2 f 104 LIT
2 f 107 geo
2 f 109 phys
2 f 110 eng
2 f 111 hist
3 p
4 f 100 bio
4 f 102 chm
4 f 109 phys
4 f 110 eng

#tempResult:

(studentid,p/f,1st,2st,3rd,4 th,5th)
1 p
2 f bio math chm spts lit
3 p
4 f bio chm phys eng


what query turns #temptable to #tempresult?.
rick




--
Sent by ricksql from yahoo in field com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new

[Jens Süßmeyer]

Re: query??

you have to put the strigs all together, bute till you don´t post some ddl
and exmplae data its not very easy to help you. The first thing, is to do
this query with a function that concas these values together:

---Untested, because of missing examples:

DROP Function fn_Myfunc

CREATE FUNCTION fn_Myfunc(@COL int)
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @W VARCHAR(30)
DECLARE @EXAM varchar(50)
SET @W = ''
SELECT @W = @W + CAST(examname AS VARCHAR(10)) + ' ' FROM temptable WHERE
studentID=@COL
SELECT @EXAM=examname FROM temptable WHERE studentID=@COL
If @EXAM IS NOT NULL
BEGIN
SET @W = LEFT(@W,LEN(@W)-1)
END
RETURN @W
END
GO

SELECT StudentID,[P/F],dbo.fn_Myfunc( StudentID) as Exams
FROM temptable

But for this, tables must be non temporary, because of working in a
function.

HTH, Jens Süßmeyer.



"ricksql@yahoo. com" <u50415039@spaw nkill.ip-mobilphone.net> schrieb im
Newsbeitrag news:l.10625637 63.1400665283@h ost-66-81-69-23.rev.o1.com.. .[color=blue]
> #temptable (studentID,p/f,examid,examna me):
> 1 p
> 2 f 100 bio
> 2 f 101 mTH
> 2 f 102 CHM
> 2 f 103 spts
> 2 f 104 LIT
> 2 f 107 geo
> 2 f 109 phys
> 2 f 110 eng
> 2 f 111 hist
> 3 p
> 4 f 100 bio
> 4 f 102 chm
> 4 f 109 phys
> 4 f 110 eng
>
> #tempResult:
>
> (studentid,p/f,1st,2st,3rd,4 th,5th)
> 1 p
> 2 f bio math chm spts lit
> 3 p
> 4 f bio chm phys eng
>
>
> what query turns #temptable to #tempresult?.
> rick
>
>
>
>
> --
> Sent by ricksql from yahoo in field com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new[/color]

[John Bell]

Re: query??

Hi

It seems you are wanting a crosstab query! Here are a list of useful links:

http://tinyurl.com/i9mt

John

"ricksql@yahoo. com" <u50415039@spaw nkill.ip-mobilphone.net> wrote in message
news:l.10625637 63.1400665283@h ost-66-81-69-23.rev.o1.com.. .[color=blue]
> #temptable (studentID,p/f,examid,examna me):
> 1 p
> 2 f 100 bio
> 2 f 101 mTH
> 2 f 102 CHM
> 2 f 103 spts
> 2 f 104 LIT
> 2 f 107 geo
> 2 f 109 phys
> 2 f 110 eng
> 2 f 111 hist
> 3 p
> 4 f 100 bio
> 4 f 102 chm
> 4 f 109 phys
> 4 f 110 eng
>
> #tempResult:
>
> (studentid,p/f,1st,2st,3rd,4 th,5th)
> 1 p
> 2 f bio math chm spts lit
> 3 p
> 4 f bio chm phys eng
>
>
> what query turns #temptable to #tempresult?.
> rick
>
>
>
>
> --
> Sent by ricksql from yahoo in field com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new[/color]

[oj]

Re: query??

If you know in advance the number of columns in the result set, try:
http://support.microsoft.com/default...;EN-US;q175574.

If you have a variable number of columns, try:

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

If you want some interesting utilities to do it for you, try:
http://www.ag-software.com/AGS/xp ags crosstab.asp

RAC at:

https://www.rac4sql.net

--
-oj
RAC v2.2 & QALite!

http://www.rac4sql.net

"ricksql@yahoo. com" <u50415039@spaw nkill.ip-mobilphone.net> wrote in message
news:l.10625637 63.1400665283@h ost-66-81-69-23.rev.o1.com.. .[color=blue]
> #temptable (studentID,p/f,examid,examna me):
> 1 p
> 2 f 100 bio
> 2 f 101 mTH
> 2 f 102 CHM
> 2 f 103 spts
> 2 f 104 LIT
> 2 f 107 geo
> 2 f 109 phys
> 2 f 110 eng
> 2 f 111 hist
> 3 p
> 4 f 100 bio
> 4 f 102 chm
> 4 f 109 phys
> 4 f 110 eng
>
> #tempResult:
>
> (studentid,p/f,1st,2st,3rd,4 th,5th)
> 1 p
> 2 f bio math chm spts lit
> 3 p
> 4 f bio chm phys eng
>
>
> what query turns #temptable to #tempresult?.
> rick
>
>
>
>
> --
> Sent by ricksql from yahoo in field com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new[/color]

[ricksql@yahoo.com]

Re: Re: query??

Dear Jens Süßmeyer,

how do it in create procedure?.
rick
[color=blue]
>
> you have to put the strigs all together, bute till you don´t post some ddl
> and exmplae data its not very easy to help you. The first thing, is to do
> this query with a function that concas these values together:
>
> ---Untested, because of missing examples:
>
> DROP Function fn_Myfunc
>
> CREATE FUNCTION fn_Myfunc(@COL int)
> RETURNS VARCHAR(30)
> AS
> BEGIN
> DECLARE @W VARCHAR(30)
> DECLARE @EXAM varchar(50)
> SET @W = ''
> SELECT @W = @W + CAST(examname AS VARCHAR(10)) + ' ' FROM temptable WHERE
> studentID=@COL
> SELECT @EXAM=examname FROM temptable WHERE studentID=@COL
> If @EXAM IS NOT NULL
> BEGIN
> SET @W = LEFT(@W,LEN(@W)-1)
> END
> RETURN @W
> END
> GO
>
> SELECT StudentID,[P/F],dbo.fn_Myfunc( StudentID) as Exams
> FROM temptable
>
> But for this, tables must be non temporary, because of working in a
> function.
>
> HTH, Jens Süßmeyer.
>[color=green]
> > #temptable (studentID,p/f,examid,examna me):
> > 1 p
> > 2 f 100 bio
> > 2 f 101 mTH
> > 2 f 102 CHM
> > 2 f 103 spts
> > 2 f 104 LIT
> > 2 f 107 geo
> > 2 f 109 phys
> > 2 f 110 eng
> > 2 f 111 hist
> > 3 p
> > 4 f 100 bio
> > 4 f 102 chm
> > 4 f 109 phys
> > 4 f 110 eng
> >
> > #tempResult:
> >
> > (studentid,p/f,1st,2st,3rd,4 th,5th)
> > 1 p
> > 2 f bio math chm spts lit
> > 3 p
> > 4 f bio chm phys eng
> >
> >
> > what query turns #temptable to #tempresult?.
> > rick
> >
> >
> >
> >
> > --
> > Sent by ricksql from yahoo in field com
> > This is a spam protected message. Please answer with reference header.
> > Posted via http://www.usenet-replayer.com/cgi/content/new[/color]
>
>[/color]








--
Spam protected message from:
Sent by ricksql from yahoo piece from com
Posted via http://www.usenet-replayer.com/cgi/content/new