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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
PortletPaulEE Topic AdvisorCommented:
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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
PortletPaulEE Topic AdvisorCommented:
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
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.