How to convert rows into column

Amit Chaudhari 1 Reputation point
2023-01-09T10:58:11.6+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,561 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Jingyang Li 4,521 Reputation points
    2023-01-09T19:36:30.427+00:00
    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  
    
    No comments

  2. CosmogHong-MSFT 6,771 Reputation points Microsoft Employee
    2023-01-10T01:50:03.487+00:00

    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 277646-image.png 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.

    No comments