Query to combine each duplicate found entry 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 &amp; 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!

Code from another forum

  SELECT [USER],  
    STUFF(b.[licenseDup],1,1,'')                                            AS licenseDup, 
    STUFF(c.[yearDup],1,1,'')                                               AS yearDup,
    STUFF(d.[makeDup],1,1,'')                                               AS makeDup,
    STUFF(e.[modelDup],1,1,'')                                              AS modelDup,
    STUFF(f.[colorDup],1,1,'')                                              AS colorDup
  FROM
    (SELECT DISTINCT a.*            FROM [FEBCardb].[dbo].[sheet1$]         AS a)           AS a
    CROSS APPLY
    (SELECT '/' + b.[License]       FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) b(licenseDup)
    CROSS APPLY
    (SELECT '/' + b.[Year]          FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) c(yearDup)
    CROSS APPLY
    (SELECT '/' + b.[Vehicle Make]  FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) d(makeDup)
    CROSS APPLY
    (SELECT '/' + b.[Vehicle Model] FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) e(modelDup)
    CROSS APPLY
    (SELECT '/' + b.[Color Name]    FROM [FEBCardb].[dbo].[sheet1$]         AS b            WHERE a.[User] = b.[User]   FOR XML PATH('')) f(colorDup);


This produces the following (what I see from that query above):

USER   |licenseDup     |yearDup   |makeDup       |modelDup         |colorDup
------------------------------------------------------------------------------
V5432E |XXXXXX/ZZZZZZZ |1998/2001 |Pontiac/Honda |Grand Am/Odyssey |Silver/Green
V5432E |XXXXXX/ZZZZZZZ |1998/2001 |Pontiac/Honda |Grand Am/Odyssey |Silver/Green
S89542 |XXXXXX         |2010      |Dodge         |Viper            |Black


And this is what I am looking to see:

USER   |licenseDup     |yearDup   |makeDup       |modelDup         |colorDup
------------------------------------------------------------------------------
V5432E |XXXXXX/ZZZZZZZ |1998/2001 |Pontiac/Honda |Grand Am/Odyssey |Silver/Green
S89542 |XXXXXX         |2010      |Dodge         |Viper            |Black
Since performance is obviously not going to be a high priority here (all this XML PATH concatenation is quite expensive), and since the only real problem with the output is that you have duplicates, try changing this:

SELECT [USER],  
    STUFF(b.[licenseDup],1,1,'')    ...
  FROM
    (SELECT DISTINCT a.* FROM [FEBCardb].[dbo].[sheet1$] ...


To this:

SELECT DISTINCT [USER],  
    STUFF(b.[licenseDup],1,1,'')    ...
  FROM
    (SELECT a.* FROM [FEBCardb].[dbo].[sheet1$] ...