Doubt in Sql query building

krsrikanth February 4, 2009 at 21:10:19
Specs: Windows XP
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


See More: Doubt in Sql query building

Report •


#1
February 5, 2009 at 04:03:29
Start by reading up on Database Normalization.

http://en.wikipedia.org/wiki/Databa...


Report •

#2
February 5, 2009 at 08:43:10
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;


Report •

#3
February 5, 2009 at 10:05:32
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?


Report •

Related Solutions


Ask Question