Reputation: 45
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
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
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
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
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