How to convert rows into column

Question

Please check attached here as per discussed use query for Pivot and make it as below requirement

PARTY TYPE_CODE NUMBER
4578 PAN xyz
4578 GSTIN 123
4578 PAN PAN1

Convert into below table.

PARTY PAN GST
4578 xyz 123
4578 PAN1

Answer 1

Create table test (PARTY int, TYPE_CODE varchar(100), NUMBER varchar(100)  
 )  
Insert into test  
values (4578,'PAN','xyz')  
,(4578,'GSTIN','123')  
 ,(4578,'PAN','PAN1')  
  
  
  
 select   
 PARTY  
 , MAX(case when TYPE_CODE='PAN' then Number else null end)  PAN    
 , MAX(case when TYPE_CODE='GSTIN' then Number else null end)    GST  
 from   
 (select *,ROW_NUMBER() Over(partition by PARTY, TYPE_CODE order by TYPE_CODE) rn   
 from test) t  
 group by PARTY,rn  
  
 drop table test  

Answer 2

Hi @Amit Chaudhari
One thing you need to know is that when you do PIVOT to this dataset, aggregation is always need.
What confused me a lot is that all rows have same PARTY 4578. It is hard to determine which PAN ('xyz' or 'PAN1') is matched with GSTIN '123'. If you check Jingyang Lì's query, you will get this output which is different from your requirement.
However, if your dataset looks like below, then PIVOT works well.

Create table #test (PARTY int, TYPE_CODE varchar(100), NUMBER varchar(100))  
Insert into #test values   
 (4578,'PAN','xyz')  
,(4578,'GSTIN','123')  
,(4588,'PAN','PAN1')  
,(4588,'GSTIN','456')  
,(4598,'PAN','PAN2')  
,(4598,'GSTIN','789')  
      
SELECT PARTY,[PAN],[GSTIN]  
FROM #test PIVOT(MAX(NUMBER)FOR TYPE_CODE IN([PAN],[GSTIN]))P  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.