I have 3 Columns in the same table: Id, Code and Rank. I want to transpose so that every id only shows up once and each rank has the specific code associated with it. I only want ranks 1,2 and 3. How do I do this?

I have 3 Columns in the same table: Id, Code and Rank. I want to transpose so that every id only shows up once and each rank has the specific code associated with it. I only want ranks 1,2 and 3. How do I do this?

Hi @saanahkhan-8584,
Could you please validate all the answers so far and provide any update?
If all are not working, please provide more sample data and expected output.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thank you for understanding!
Best regards
Melissa
SELECT Id, MIN(CASE Rank WHEN 1 THEN Code END) AS Rank1,
MIN(CASE Rank WHEN 2 THEN Code END) AS Rank2,
MIN(CASE Rank WHEN 3 THEN Code END) AS Rank3
FROM tbl
GROUP BY id
Hi @saanahkhan-8584,
Welcome to Microsoft Q&A!
Please also refer below using Pivot:
create table Tablesa
(
ID int,
Code varchar(4),
Rank int)
insert into Tablesa values
(1,'F17',1),
(1,'F13',2),
(2,'F20',1),
(2,'F02',2),
(2,'F30',3)
select id,isnull(Rank1,0) Rank1 ,isnull(Rank2,0) Rank2 ,isnull(Rank3,0) Rank3
from
(select id,code,'Rank'+cast(rank as char(1)) Rank from Tablesa) s
pivot
(max(code) for rank in ([Rank1],[Rank2],[Rank3])) p
Output:
id Rank1 Rank2 Rank3
1 F17 F13 0
2 F20 F02 F30
If you would not like to list the Rank1,2,3 and so on manually, you could consider to use dynamic statement as below:
declare @sql nvarchar(max)
declare @sql1 nvarchar(max)
select @sql=STUFF(( SELECT distinct ',[Rank' + cast(rank as char(1))+']' FROM Tablesa FOR XML PATH('') ), 1, 1, '')
select @sql1=STUFF(( SELECT distinct ',isnull(Rank' + cast(rank as char(1))+',0) Rank'+cast(rank as char(1)) FROM Tablesa FOR XML PATH('') ), 1, 1, '')
set @sql=N'select id,'+ @sql1+' from
(select id,code,''Rank''+cast(rank as char(1)) Rank from Tablesa) s pivot
(max(code) for rank in ('+@sql+')) p'
EXECUTE sp_executesql @sql
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
11 people are following this question.