Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1

    Question Unanswered: MS SQL 2012 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:
    Code:
    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:
    Code:
    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. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    ; WITH c1 AS (
    SELECT *
    ,  Row_Number() OVER (PARTITION BY id ORDER BY tag) AS y_up
    ,  Row_Number() OVER (PARTITION BY id ORDER BY tag DESC) AS y_dn
       FROM (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)
       ) AS z1 (Name, ID, Tag, Address, carNum)
    ), c2 AS (
    SELECT c1.*
    ,  Cast(c1.tag AS VARCHAR) AS taglist
       FROM c1
       WHERE 1 = y_up
    UNION ALL SELECT c1.*
    ,  Cast(c2.taglist + ', ' + Cast(c1.tag AS VARCHAR) AS VARCHAR)
       FROM c1
       INNER JOIN c2
          ON c1.id = c2.id
    	  AND c1.y_up = c2.y_up + 1
    )
    SELECT Name, ID, TagList, Address, CarNum
       FROM c2
       WHERE 1 = y_dn
       ORDER BY ID
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •