CODEWITHSUNDEEP
sqlsql-server
Abdelrahman Maged
Abdelrahman Maged

Reputation: 45

Remove duplicate rows as an additional column

I have a sql table for student records and I have some duplicate rows for the student dimension cause of the major, so now I have something like this:

ID Major
----------
 1  CS
 1  Mgt

What I want is to combine this two rows in this form:

ID Major Major2
----------
 1   CS   Mgt

Upvotes: 0

Views: 53

Answers (4)

Muhammad Hassan
Muhammad Hassan

Reputation: 505

You can use pivot function directly

SELECT  [ID],[CS] AS Major , [Mgt] AS Major2 from Your_Table_Name
PIVOT
(max(Major)for [Major] IN ([CS] , [Mgt]))as p

Upvotes: 0

Pankaj Kumar
Pankaj Kumar

Reputation: 47

This will help you

Select 
ID,
(select top 1 Major from <Your_Table> where id=T.Id order by Major) Major,
(case when count(Id)>1 then (select top 1 Major from #temp where id=T.Id order by Major desc) else null end) Major2
from <Your_Table> T
Group By
ID

Upvotes: 0

Davide Lorenzo MARINO
Davide Lorenzo MARINO

Reputation: 26946

If you have at most two different values of major:

select  a.id as id, 
        a.major as major, 
        b.major as major2
from    YOUR_TABLE a
left join YOUR_TABLE b on
a.id = b.id
and (b.major is null or a.major > b.major)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You need a number for pivoting. Then you can pivot using either pivot or conditional aggregation:

select id,
       max(case when seqnum = 1 then major end) as major_1,
       max(case when seqnum = 2 then major end) as major_2
from (select t.*,
             row_number() over (partition by id order by (select null)) as seqnum
      from t
     ) t
group by id;

Note: you should validate that "2" is large enough to count the majors. You can get the maximum using:

select top 1 id, count(*)
from t
group by id
order by count(*) desc;

Upvotes: 2

Related Questions