SQLTeam.com | Weblogs | Forums

MS SQL 2012 query to combine each duplicate found entry

tsql
sql2012

#1

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!


#2
SELECT
	a.NAME,
	a.ID,
	a.ADDRESS,
	a.carNum,
	STUFF(b.Tags,1,1,'') AS Tags
FROM
	(
		SELECT DISTINCT 
			a.NAME,
			a.ID,
			a.ADDRESS,
			a.carNum
		FROM
			YourTable AS a
	) AS a
	CROSS APPLY
	(
		SELECT
			'/' + b.[Tag]
		FROM
			YourTable AS b
		WHERE
			a.Id = b.ID
		FOR XML PATH('')
	) b(Tags);

#3

Thank you for the quick response! However, this doesnt seem to combine the found records into one.

Example (what i see)

Name        | ID    |Tag    |Address            |carNum |Tags
-----------------------------------------------------------------------------
Bob Barker  |2054   |52377  |235 Some road      |9874   |52377/97011/40019
Bill Gates  |5630   |69471  |014 Washington Rd. |3700   |69471
Bob Barker  |2054   |97011  |235 Some road      |9874   |52377/97011/40019
Bob Barker  |2054   |40019  |235 Some road      |9874   |52377/97011/40019

Instead of doing it this way (the way I need to see it):

Name        | ID    |Tag    |Address            |carNum |Tags
-------------------------------------------------------------------------
Bob Barker  |2054   |52377  |235 Some road      |9874   |52377/97011/40019
Bill Gates  |5630   |69471  |014 Washington Rd. |3700   |69471

#4

The desired output in your original posting had only 5 columns, but in the latest post, your desired output has six columns. The extra column being Tag.

Given that there are 3 possible tags for Bob Barker, how do you decide which of those tags to show in your output. You picked 52377 in your sample output. What is the logic for picking that as opposed to the others?


#5

Check my last post again. The extra field at the end (Tags) is what your code generated.