MS SQL 2012 query to combine each duplicate found entry

Question

Hey all I am wanting to create a query so that I can combine each of the found duplicates into one entry.

An example of this is:

Name        | ID    | Tag   | Address           |carNum
-------------------------------------------------------
Bob Barker  |2054   |52377  |235 Some road      |9874
Bill Gates  |5630   |69471  |014 Washington Rd. |3700
Bob Barker  |2054   |97011  |235 Some road      |9874
Bob Barker  |2054   |40019  |235 Some road      |9874
Steve Jobs  |8501   |73051  |100 Infinity St.   |4901
John Doe    |7149   |86740  |7105 Bull Rd.      |9282
Bill Gates  |5630   |55970  |014 Washington Rd. |3700
Tim Boons   |6370   |60701  |852 Mnt. Creek Rd. |7059

In the example above, Bob Barker and Bill gates are both in the database more than once so I would like the output to be the following:

Bob Barker|2054|52377/97011/40019       |235 Some road     |9874
Bill Gates|5630|69471/55970             |014 Washington Rd.|3700
Steve Jobs|8501|73051                   |100 Infinity St.  |4901
John Doe  |7149|86740                   |7105 Bull Rd.     |9282
Tim Boons |6370|60701                   |852 Mnt. Creek Rd.|7059

Notice how Bob Barker & Bill Gates appends the tag row (the duplicated data) into one row instead of having multiple rows. This is because I do not want to have to check the previous ID and see if it matches the current id and append to the data.

I am hoping a SQL query guru would have a query to do this for me!

Thanks for your time and help!

------------------------------------------------------------------------------------------------------------------------

Question has changed from ACCESS DATABASE to MS SQL SERVER 2012 database

------------------------------------------------------------------------------------------------------------------------

You are looking for an aggregate string concatenation functions. MS Access doesn't have one. Here is one way to write such a function: http://stackoverflow.com/questions/5174362/microsoft-access-condense-multiple-lines-in-a-table/5174843#5174843. — Gordon Linoff, Nov 19 '15 at 14:52
Is the distinct keyword not available? select distinct col1, col2... from tablename — Shyamal Desai, Nov 19 '15 at 14:57
I'm now going to use MS SQL Server 2012 for the query since it has more functionality than Access. — StealthRT, Nov 19 '15 at 16:14
this is the same as making a comma separated list (just use / instead of comma). There are many Q+As for this on SO. It is a duplicate. Here is my answer: http://stackoverflow.com/a/1785923/215752 — Hogan, Nov 19 '15 at 16:16

score 2 · Answer 1 · answered Nov 19 '15 at 15:09

2

Use MySQL GROUP_CONCAT for Tag field and group by data with Name field.

Query:
SELECT Name,  ID, GROUP_CONCAT(Tag SEPARATOR '/') AS Tag, Address, carNum 
FROM users GROUP BY Name

answered Nov 19 '15 at 15:09

Simhachalam Gulla

996
7
16

Does this query work in Access 2010? – StealthRT Nov 19 '15 at 15:26
Please check my OP for an update. – StealthRT Nov 19 '15 at 16:13

Peter Elzinga · Answer 2 · 2015-11-20T08:09:35.277

You can do it like this:

CREATE TABLE MyTable    ( Name      nvarchar(50)
                        , ID        int
                        , Tag       int
                        , Address   nvarchar(50)
                        , carNum    int
                        )

INSERT INTO MyTable VALUES
  ('Bob Barker', 2054, 52377, '235 Some road'       , 9874)
, ('Bill Gates', 5630, 69471, '014 Washington Rd.'  , 3700)
, ('Bob Barker', 2054, 97011, '235 Some road'       , 9874)
, ('Bob Barker', 2054, 40019, '235 Some road'       , 9874)
, ('Steve Jobs', 8501, 73051, '100 Infinity St.'    , 4901)
, ('John Doe'  , 7149, 86740, '7105 Bull Rd.'       , 9282)
, ('Bill Gates', 5630, 55970, '014 Washington Rd.'  , 3700)
, ('Tim Boons' , 6370, 60701, '852 Mnt. Creek Rd.'  , 7059)

SELECT  YT.Name
        , ID
        , LEFT(YT.SUB, LEN(YT.SUB) - 1) AS Tags
        , Address
        , carNum
FROM    (SELECT DISTINCT
                Name
                , ( SELECT      CAST(ST1.Tag AS nvarchar(5)) + ',' AS [text()]
                    FROM        MyTable ST1
                    WHERE       ST1.ID = ST2.ID
                    ORDER BY    ST1.Name
                FOR
                 XML PATH('')
                ) SUB
              , ID
              , Address
              , carNum
         FROM   MyTable ST2
        ) YT

DROP TABLE MyTable

MS SQL 2012 query to combine each duplicate found entry

2 Answers2