query??

[ricksql]

#temptable (studentID,p/f,examid,examname):
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,4th,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]

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.

"ri*****@yahoo.com" <u5*******@spawnkill.ip-mobilphone.net> schrieb im
Newsbeitrag news:l.*********************@host-66-81-69-23.rev.o1.com...

#temptable (studentID,p/f,examid,examname):
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,4th,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

[John Bell]

Hi

It seems you are wanting a crosstab query! Here are a list of useful links:
http://tinyurl.com/i9mt
John

"ri*****@yahoo.com" <u5*******@spawnkill.ip-mobilphone.net> wrote in message
news:l.*********************@host-66-81-69-23.rev.o1.com...

#temptable (studentID,p/f,examid,examname):
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,4th,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

[oj]

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...rticleID=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:
www.rac4sql.net

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"ri*****@yahoo.com" <u5*******@spawnkill.ip-mobilphone.net> wrote in message
news:l.*********************@host-66-81-69-23.rev.o1.com...

#temptable (studentID,p/f,examid,examname):
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,4th,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

[ricksql]

Dear Jens Süßmeyer,

how do it in create procedure?.
rick

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.

#temptable (studentID,p/f,examid,examname):
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,4th,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

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