How to stuff duplicate records in SQL server?

Question

Possible Duplicate:
Simulating group_concat MySQL function in MS SQL Server 2005?
SQL Query for aggregation/concatenation

I am trying to use the stuff function in SQL server to stuff certain info. Here is the example:

Money     Age    Gender
860       9          F
860       15         M
860       15         M
860       16         M
860       16         F

I would like to stuff the Age and Gender column so that only one record will display as following:

Money   Age                 Gender
860     9, 15, 15, 16, 16   F, M, M, M, F

Please note, I would like to keep the two 15s and three M in the Age and Gender respectively.

Steve Stedman · Accepted Answer · 2012-04-08T17:37:25.773

1

This is easier to do using a FOR XML rather than stuff. Keep in mind that the FORM XML clause can be tricked to generate a comma seperated list (CSV).

The example below should do exactly what you are asking for.

CREATE TABLE moneyagegender 
( 
 [Money]  INT, 
 [Age]    INT, 
 [Gender] VARCHAR(2) 
); 

INSERT INTO moneyagegender 
VALUES      (860, 9, 'F'), 
        (860, 15, 'M'), 
        (860, 15, 'M'), 
        (860, 16, 'M'), 
        (860, 16, 'F'); 

SELECT mag.money, 
   (SELECT Substring((SELECT ', ' + CAST(m2.age AS VARCHAR(1024)) 
                      FROM   moneyagegender m2 
                      WHERE  m2.money = mag.money 
                      ORDER  BY m2.age 
                      FOR XML PATH('')), 3, 10000000) AS list) AS ages, 
   (SELECT Substring((SELECT ', ' + m3.gender 
                      FROM   moneyagegender m3 
                      WHERE  m3.money = mag.money 
                      ORDER  BY m3.age 
                      FOR XML PATH('')), 3, 10000000) AS list) AS genders 
FROM   moneyagegender mag 
GROUP  BY mag.money;

and here is the output.

Money       Ages                 Genders
----------- -------------------- -----------------
860         9, 15, 15, 16, 16    F, M, M, M, F
(1 row(s) affected)

I hope this helps.

If you need more details, I have a blog posting from last year that explains this. http://stevestedman.com/2011/10/converting-part-of-a-result-set-to-a-comma-separated-list/

edited Apr 08 '12 at 17:37

answered Apr 08 '12 at 17:20

Steve Stedman

2,632
3
21
21

In the [question](http://stackoverflow.com/questions/7896239/sql-query-for-aggregation-concatenation) I linked to [Joe Stefanelli](http://stackoverflow.com/users/341251/joe-stefanelli) explains that `STUFF` is only used to remove the unwanted first separator (in a much cleaner way than `substring(,3,10000000)`. – GSerg Apr 08 '12 at 18:23
@ Steve and GSerg, both answers worked perfectly. Many thanks! – Hung Captain Apr 08 '12 at 21:22
@ Steve, suppose i have more than one columns (in this case let say date_time, location, Store and then Money column which in was the orginal from this script, would it work? – Hung Captain Apr 08 '12 at 21:40
Yes, this way of doing things will work for one or more columns. In the example I showed 2 subqueries to pull the data for 2 columns, but there is not reason that you couldn't do the same thing for many columns. – Steve Stedman Apr 09 '12 at 01:08
@SteveStedman is there anyway to remove the duplicates? like **15**, **M** and **F** – rrk Jul 23 '15 at 08:22
YES, just add DISTINCT to the SELECT statements inside the substrings. – Steve Stedman Jul 24 '15 at 22:01

How to stuff duplicate records in SQL server?

1 Answers1