How to pull the duplicate record in SQL

Question

I have some records that looks like this.

enter image description here

I want to pull out as the following.

Second Pic

Is this possible?

I can only pull out the duplicate values but can't get that I want.

SELECT [COLUMN A] , COUNT([COLUMN A]) FROM [MYTABLE] GROUP BY [COLUMN A] HAVING COUNT([COLUMN A]) >1 ORDER BY [COLUMN A]

Anyone please help me. Thanks.

Why are there three "Column A" columns in your first graphic? — paxdiablo, Apr 24 '14 at 03:30
Thanks for your prompt reply. You all are so fast. :D I added wrong grapic. :D — Lamin, Apr 24 '14 at 04:34

score 1 · Accepted Answer · answered Apr 24 '14 at 03:37

1

You can use the following query to get the desired result:

SELECT 
    ColumnA
    , MAX(ColumnB) ColumnB
    , MIN(ColumnC) ColumnC
    , MAX(ColumnC) ColumnD
FROM MYTABLE
GROUP BY ColumnA
ORDER BY ColumnA;

answered Apr 24 '14 at 03:37

Joseph B

Hi Joseph, I want to ask you another Q concerning with this. If Column A has more than 2 times with different Column C, how should I do? – Lamin Apr 24 '14 at 05:38
If you have more than 2 distinct values for Column C, you can consider "pivoting" the table. Check this related question out - http://stackoverflow.com/questions/15674373/pivot-rows-to-columns-without-aggregate – Joseph B Apr 24 '14 at 12:39

score 0 · Answer 2 · answered Apr 24 '14 at 03:33

Try in the 'Count()' sentence add the id or * i.e.:

SELECT [COLUMN A] , COUNT(*) FROM [MYTABLE] GROUP BY [COLUMN A] HAVING COUNT(*) >1 ORDER BY [COLUMN A]

or

SELECT [COLUMN A] , COUNT(´id_col_A´) FROM [MYTABLE] GROUP BY [COLUMN A] HAVING COUNT(´id_col_A´) >1 ORDER BY [COLUMN A]

2 Answers2