This should do it, but be careful with the delete at the end.Create Table #myTable (name varchar(10), Qual varchar(10), notes varchar(100), allNotes varchar(100))Insert Into #myTable (name, Qual, notes)Select 'Simon', 'spanish', 'Distinction' UnionSelect 'Simon', 'spanish', 'Pass' UnionSelect 'Simon', 'german', 'A Level' UnionSelect 'Stewart', 'spanish', 'Degree' UnionSelect 'Stewart', 'spanish', '2:1' UnionSelect 'Martin', 'english', 'A level'Select * From #myTableDeclare @curKey varchar(100), @Notes varchar(100)Update #myTableSet @Notes = case when isnull(@curKey,'')<>name+'|'+Qual then notes else @notes + ', ' + notes end, @curKey = name+'|'+Qual, AllNotes = @notesFrom #myTableDelete AFrom #myTable AWhere len(Allnotes)<(Select max(len(Allnotes)) From #myTable B Where name = A.name and Qual = A.Qual)Select * From #myTableDrop Table #myTable
Corey
Secret Service Agent: Mr. President, you're urinating on me.President Lyndon Johnson: I know I am. It's my prerogative. 
combining string fields
Posted - 2005-05-19 : 06:46:17
i've got a table holding a list of peoples qualifications. however, some people have multiple instances of the same qualification but different notes. i want to combine the notes field to reduce the number of rows from many to one. Example of what it currently is:Name Qual NotesSimon spanish DistinctionSimon spanish PassSimon german A LevelStewart spanish DegreeStewart spanish 2:1Mrtin english A level This is what i want it to look like:Name Qual NotesSimon spanish Distinction Pass A levelSimon german A LevelStewart spanish Degree 2:1Martin english A leveli was trying to find some kind of grouping function, but without success. my only alternative would be to use a cursor to step through the records. is there a better way?any help would be gratefully received!Simon