sum() for textual fields?

[Zaka Ferenc]

Hi all,

Many times would be useful (for me at least) if sum() could summarize
textual fields by simply concatenating them :

eg a table named 'lessons' contains
Lesson Teacher
math Mr. Brown
history Mr. Brown
math Ms. White
gym Mr. Green
geo Ms. White

so I could use a select like this:

select Lesson,sum(Teacher) group by Lesson

(and sum might take a second parameter as a separator between added fields)

Of course, these things aren't exists so I wanted to write my own
"textsum()" function, but in MSSQL2000 user functions can take only
scalar variables AFAIK.

Anyone has faced and/or has solved this problem other way (with a simple
select)?

Thanks

zf

[Zaka Ferenc]

:-))

Posting my question I've noticed that the previous post asks the very
same thing as my letter asks.
Anyway I would be grateful for answers....

Zaka Ferenc wrote:

Hi all,

Many times would be useful (for me at least) if sum() could summarize
textual fields by simply concatenating them :

eg a table named 'lessons' contains
Lesson Teacher
math Mr. Brown
history Mr. Brown
math Ms. White
gym Mr. Green
geo Ms. White

so I could use a select like this:

select Lesson,sum(Teacher) group by Lesson

(and sum might take a second parameter as a separator between added fields)

Of course, these things aren't exists so I wanted to write my own
"textsum()" function, but in MSSQL2000 user functions can take only
scalar variables AFAIK.

Anyone has faced and/or has solved this problem other way (with a simple
select)?

Thanks

zf

[David Portas]

CREATE TABLE Lessons (lesson VARCHAR(10), teacher VARCHAR(10), PRIMARY KEY
(lesson, teacher))

INSERT INTO Lessons VALUES ('math','Mr. Brown')
INSERT INTO Lessons VALUES ('history','Mr. Brown')
INSERT INTO Lessons VALUES ('math','Ms. White')
INSERT INTO Lessons VALUES ('gym','Mr. Green')
INSERT INTO Lessons VALUES ('geo','Ms. White')

SELECT lesson,
MIN(CASE seq WHEN 1 THEN teacher END)+
COALESCE(', '+MIN(CASE seq WHEN 2 THEN teacher END),'')+
COALESCE(', '+MIN(CASE seq WHEN 3 THEN teacher END),'')+
COALESCE(', '+MIN(CASE seq WHEN 4 THEN teacher END),'')+
COALESCE(', '+MIN(CASE seq WHEN 5 THEN teacher END),'')
FROM
(SELECT L1.lesson, L2.teacher, COUNT(*) AS seq
FROM Lessons AS L1
JOIN Lessons AS L2
ON L1.lesson = L2.lesson
AND L1.teacher<=L2.teacher
GROUP BY L1.lesson, L2.teacher) AS X
GROUP BY lesson

--
David Portas
------------
Please reply only to the newsgroup
--