GO
CREATE FUNCTION CsvForm ( @p_sec varchar(5))
RETURNS VARCHAR(4000)
AS
BEGIN
	DECLARE @a varchar(4000) = ''
	SELECT @a = @a + student + ',' FROM tablename WHERE sec = @p_sec
	RETURN LEFT(@a, LEN(@a)-1)
END
GO
SELECT sec, dbo.CsvForm(sec) names, MAX(marks) maxMark
FROM tablename
GROUP BY sec

quote:

---

Originally posted by nigelrivett

Does sql server 2000 include xml statements.
Try the example at the top here - think you will need to change the table variable to a temp table

http://www.nigelrivett.net/SQLTsql/CSVStringSQL.html

---

quote:

---

Originally posted by nigelrivett

How about this

create table #a (sec varchar(10), student varchar(10), marks int)
insert #a select 'A', 'ram', 20
insert #a select 'A', 'raj', 50
insert #a select 'B', 'ramu', 80
insert #a select 'B', 'dines', 99
insert #a select 'B', 'xxxx', 10

create table #b (sec varchar(10), student varchar(1000), marks int, laststudent varchar(10))
insert #b select sec, min(student), max(marks), min(student) from #a group by sec
while exists (select * from #a a join #b b on a.sec = b.sec where a.student > b.laststudent)
begin
update b
set student = b.student + ',' + a.student ,
laststudent = a.student
from #b b
join #a a
on a.sec = b.sec
and a.student = (select min(a2.student) from #a a2 where a2.sec = b.sec and a2.student > b.laststudent)
end

select * from #b


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---