CODEWITHSUNDEEP
sql
John F.
John F.

Reputation: 4840

SQL Query - Displaying the same column twice under different conditions

I am wondering if the following is possible. Say I have the following table:

ID | NAME
 1 | John
 2 | Bob
 3 | John
 4 | Bob


Is it possible to run a query that results in the following:

NAME| ID1 | ID2
John    | 1    | 3
Bob     | 2    | 4

EDIT

Sorry for the confusion. My question addresses instances where I need to handle the possibility of 2 duplicates for a large data set.

Upvotes: 0

Views: 3540

Answers (3)

user330315
user330315

Reputation:

If there are exactly two rows with each name, then the following should work:

 SELECT a.name,
        a.id as id1,
        b.id as id2
 FROM the_table a 
   JOIN the_table b ON a.name = b.name AND a.id <> b.id

Upvotes: 1

Matt Smucker
Matt Smucker

Reputation: 5234

Assuming exactly 2 duplicates

SELECT 
   NAME, 
   MIN(ID) as ID1,
   MAX(ID) as ID2
FROM Table t
GROUP BY NAME

Upvotes: 9

user554546
user554546

Reputation:

This should work. Note that the subquery screens out all names that don't have exactly two ids.

select name,min(id) as id1,max(id) as id2
from table
join(
    select name
    from table
    group by name
    having count(1)=2
)names
using(name)
group by name;

Upvotes: 1

Related Questions