I have some records in a table that looks like this: 5619,Marcus Lindsay Asher,2005/06,3500,525 5806,John Clifford,1999/00,3000,450 5806,John Clifford,2000/01,2000,300 5887,Geoffery William,2005/06,5000,750 I would like to combine duplicate records into a single record with the new single record containing additional fields (for example the last 3 fields). In the example file above, the first field is the unique field. So I would like my output to be like below: 5619,Marcus Lindsay Asher,2005/06,3500,525 5806,John Clifford,1999/00,3000,450,2000/01,2000,300 5887,Geoffery William,2005/06,5000,750 Can someone please please help me with a solution using Sql Server 2005

Start by reading up on Database Normalization. http://en.wikipedia.org/wiki/Databa...

heh, i dont think its possible to make a query returning different number of colums. not sure if this going to work on sql server, but in SQL Languange, for combinining records, you can use group by clause. SELECT ID, NAME, MIN(DATE) & " - " & MAX(DATE) as PERIOD, SUM(FIELD4), SUM(FIELD5), COUNT(*) as NumRecords FROM TABLENAME GROUP BY ID, NAME;

Hm yeah you've got me thinking. I know mysql has got the GROUP_CONCAT() method of summarising data, I wonder if the same could apply here... Note: code is in mysql SELECT id, emp_name, GROUP_CONCAT(CONCAT(fin_year,',',first_amt,',',second_amt)) FROM table GROUP BY id, emp_name It's messy but it should give you pretty close to what you're after. Unfortunately SQL 2005 doesn't have this function, but http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html ...is maybe somewhere to start?