question

saanahkhan-8584 avatar image
0 Votes"
saanahkhan-8584 asked MelissaMa-msft commented

Transpose From the Same Table

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?


96040-image.png


sql-server-transact-sql
image.png (4.6 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered
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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.