help me out for a simple query

I am sending a small scenario
i have a table
CREATE TABLE STUDENT_ANSWERS(EXAMID INT, MARKS INT);
INSERT INTO VALUES (1, 20)
INSERT INTO VALUES (1, 10)
INSERT INTO VALUES (1, 30)
INSERT INTO VALUES (1, 50)
INSERT INTO VALUES (2, 50)
INSERT INTO VALUES (2, 70)
INSERT INTO VALUES (2, 20)
INSERT INTO VALUES (2, 40)
INSERT INTO VALUES (2, 90)
i need the output like
examid marksstring
----
1 20,10,30,50
2 50,70,20,40,90
----
thx for ur help
*** Sent via Developersdex http://www.examnotes.net ***Here's one way to achieve this using a function:
CREATE FUNCTION dbo.fn_ConcatMarks(@.id INT) RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.marks VARCHAR(8000)
SET @.marks = ''
SELECT @.marks = @.marks + CAST(MARKS AS VARCHAR(10)) + ',' FROM
STUDENT_ANSWERS
WHERE EXAMID = @.id
RETURN LEFT(@.marks, LEN(@.marks) - 1)
END
GO
SELECT EXAMID, dbo.fn_ConcatMarks(EXAMID) AS marks
FROM STUDENT_ANSWERS
GROUP BY EXAMID
EXAMID marks
-- --
1 20,10,30,50
2 50,70,20,40,90
You can find several other solutions in previous threads if you look for the
keywords PIVOT, crosstab.
BG, SQL Server MVP
www.SolidQualityLearning.com
"kamal hussain" <skkamalh@.rediffmail.com> wrote in message
news:OzYlXrmRFHA.1208@.TK2MSFTNGP10.phx.gbl...
>
> I am sending a small scenario
> i have a table
> CREATE TABLE STUDENT_ANSWERS(EXAMID INT, MARKS INT);
> INSERT INTO VALUES (1, 20)
> INSERT INTO VALUES (1, 10)
> INSERT INTO VALUES (1, 30)
> INSERT INTO VALUES (1, 50)
> INSERT INTO VALUES (2, 50)
> INSERT INTO VALUES (2, 70)
> INSERT INTO VALUES (2, 20)
> INSERT INTO VALUES (2, 40)
> INSERT INTO VALUES (2, 90)
>
>
> i need the output like
> examid marksstring
> ----
> 1 20,10,30,50
> 2 50,70,20,40,90
> ----
>
> thx for ur help
>
>
> *** Sent via Developersdex http://www.examnotes.net ***