How to stuff duplicate records in SQL server? [duplicate]

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-08 17:37:25Z

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 8, 2012 at 17:37

answered Apr 8, 2012 at 17:20

Steve Stedman

2,6723 gold badges22 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

GSerg Over a year ago

In the question I linked to Joe Stefanelli explains that STUFF is only used to remove the unwanted first separator (in a much cleaner way than substring(,3,10000000).

Hung Captain Over a year ago

@ Steve and GSerg, both answers worked perfectly. Many thanks!

Hung Captain Over a year ago

@ 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?

Steve Stedman Over a year ago

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.

rrk Over a year ago

@SteveStedman is there anyway to remove the duplicates? like 15, M and F

|

Collectives™ on Stack Overflow

How to stuff duplicate records in SQL server? [duplicate]

1 Answer 1

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Linked

Related