sql find duplicate records in same row then display on same row

I want to find duplicates in one Column, then display it on one Row. For instance:

DATA:

Row 1:  ID1 Test1
Row 2:  ID2 Test2
Row 3:  ID1 Test3
Row 4:  ID3 Test4
Row 5:  ID3 Test5

Output:
Row1:  ID1 Test1 Test3
Row2:  ID3 Test4 Test5
LVL 1
TechneutAsked:
Who is Participating?
 
TechneutAuthor Commented:
Thanks guys, but I have figured it out with a friend. The final code I used:

;with mycte as
(
select ID, value,
row_number() over (partition by ID order by ID) as rownum
 from MyTable
)
Select t1.Value,t2.Value from
(Select ID,value from mycte where rownum=1) t1
join
(select ID,value from mycte where rownum=2) t2
On t1.ID=t2.ID


This worked exactly as I needed. I'm not sure either of your methods worked, but I'm sure they would have or were very close. Really appreciate the response on this one.
0
 
Brian CroweDatabase AdministratorCommented:
Providing schema helps in these examples.

SELECT DISTINCT ID,
   (
      SELECT ' ' + TestString
      FROM myTable
      WHERE ID = T.ID
      FOR XML PATH('')
   ) AS TestSTrings
FROM myTable AS T
0
 
PortletPaulfreelancerCommented:
SELECT
       dups.id
     , oa.TestConcat
FROM (
      SELECT ID
      FROM MyTable
      GROUP BY id
      HAVING COUNT(*) > 1
      ) DUPS
OUTER APPLY (
      SELECT ' ' + TestString
      FROM myTable
      WHERE ID = DUPS.ID
      FOR XML PATH('')
      ) OA (TestConcat)

Result:
| id |   TestConcat |
|----|--------------|
|  1 |  Test1 Test3 |
|  3 |  Test4 Test5 |
        

Open in new window


Details:
    CREATE TABLE MyTable
        ([ID] int, [TestString] varchar(5))
    ;
        
    INSERT INTO MyTable
        ([ID], [TestString])
    VALUES
        (1, 'Test1'),
        (2, 'Test2'),
        (1, 'Test3'),
        (3, 'Test4'),
        (3, 'Test5')
    ;
    
**Query 1**:

    SELECT
           dups.id
         , oa.TestConcat
    FROM (
          SELECT ID
          FROM MyTable
          GROUP BY id
          HAVING COUNT(*) > 1
          ) DUPS
    OUTER APPLY (
          SELECT ' ' + TestString
          FROM myTable
          WHERE ID = DUPS.ID
          FOR XML PATH('')
          ) OA (TestConcat)

**[Results][2]**:
    | id |   TestConcat |
    |----|--------------|
    |  1 |  Test1 Test3 |
    |  3 |  Test4 Test5 |

  [1]: http://sqlfiddle.com/#!6/31311/1
  [2]: http://sqlfiddle.com/#!6/31311/1/0

Open in new window


{+edit}
You can also remove the leading space introduced to the concatenation by using STUFF(), like this:
SELECT
       dups.id
     , oa.TestConcat
FROM (
      SELECT ID
      FROM MyTable
      GROUP BY id
      HAVING COUNT(*) > 1
      ) DUPS
OUTER APPLY (
      SELECT STUFF((
            SELECT ' ' + TestString
            FROM myTable
            WHERE ID = DUPS.ID
            FOR XML PATH('')
       ), 1, 1, '')
      ) OA (TestConcat)

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TechneutAuthor Commented:
Okay, so I apologize. After trying out the two scripts I see that I did not articulate myself correctly. The chart should had helped, but I see that misleading column and row is a significant issue.

The correct question should had been, "find duplicate in column (id), then display the values (value) in the same row".

DATA:

Row 1:  ID1 Test1
Row 2:  ID2 Test2
Row 3:  ID1 Test3
Row 4:  ID3 Test4
Row 5:  ID3 Test5

Output:
Row1:  ID1 Test1 Test3
Row2:  ID3 Test4 Test5
0
 
PortletPaulfreelancerCommented:
Actually believe my approach not only works, but is more efficient.

Plus, if there are more than two records for an id my approach will list all. The join approach is limited.
0
 
PortletPaulfreelancerCommented:
Consider this set of data:

CREATE TABLE MyTable
    ([ID] int, [TestString] varchar(50))
;
   
INSERT INTO MyTable
    ([ID], [TestString])
VALUES
    (1, 'Test1'),
    (2, 'Test2'),
    (1, 'Test3'),
    (1, 'Test3a'), --<< new
    (3, 'Test4'),
    (3, 'Test5')
;

Using the join approach this is the result:
| Value |  Value |
|-------|--------|
| Test3 | Test3a |
| Test4 |  Test5 |

Open in new window


Note how "Test1" is excluded from that result

Using the approach I proposed, this is the result:
| id |          TestConcat |
|----|---------------------|
|  1 |  Test1 Test3 Test3a |
|  3 |         Test4 Test5 |

Open in new window


The 2 approaches may be compared here: http://sqlfiddle.com/#!6/baf30/1
0
 
TechneutAuthor Commented:
Done
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.