Pivot table

Jayraj Parsa 1 Reputation point
2020-08-27T01:28:14.573+00:00

I have below table structure.

Id | Question No | Question | Answer

1 | 1 | Name | John
1 | 2 | Address | Hawaii St
2 | 1 | Name | Doe
2 | 2 | Name | msdn st

I have total 5 questions and 5 answers consistently. I'm looking for output in below format

Id | Name | Address

1 | John | Hawaii St
2 | Doe | msdn st

ANy help much appreciated

{count} votes

3 answers

Sort by: Most helpful
  1. Jingyang Li 4,521 Reputation points
    2020-08-27T01:28:53.093+00:00
    CREATE TABLE mytable(
       id   INTEGER  NOT NULL  
      ,gid  INTEGER  NOT NULL
      ,col1 VARCHAR(9) NOT NULL
      ,col2 VARCHAR(10) NOT NULL
    );
    INSERT INTO mytable(id,gid,col1,col2) VALUES
     (1,1,'Name','John')
    ,(1,2,'Address','Hawaii St')
    ,(2,1,'Name','Doe')
    ,(2,2,'Name','msdn st');
    
    
    select id
    ,max(case when gid=1 then col2 else null end) c1 
    ,max(case when gid=2 then col2 else null end) c2
    /*,max(case when gid=3 then col2 else null end) c3 
    ,max(case when gid=4 then col2 else null end) c4
    ,max(case when gid=5 then col2 else null end) c5 
    */
    from mytable
    group by id
    
    drop TABLE mytable 
    
    No comments

  2. EchoLiu-MSFT 14,416 Reputation points
    2020-08-27T01:45:39.06+00:00

    Hi @Jayraj Parsa
    I made some modifications to your inserted data.when using pivot, column names and values need to correspond. Then you can use pivot to get the output you expect:

     CREATE TABLE mytable(  
            id   INTEGER  NOT NULL    
           ,gid  INTEGER  NOT NULL  
           ,col1 VARCHAR(9) NOT NULL  
           ,col2 VARCHAR(10) NOT NULL  
         );  
         INSERT INTO mytable(id,gid,col1,col2) VALUES  
          (1,1,'Name','John')  
         ,(1,2,'Address','Hawaii St')  
         ,(2,1,'Name','Doe')  
         ,(2,2,'Address','msdn st');  
          
           
        select * from (select id,col1,col2 from mytable) as t    
        pivot (max(col2) for col1 in (Name,Address)) as p  
              
        drop TABLE mytable   
    

    20761-image.png

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Best Regards
    Echo

    No comments

  3. EchoLiu-MSFT 14,416 Reputation points
    2020-08-27T02:15:23.123+00:00

    Hi @Jayraj Parsa

    Besides,I tested the method of the first answer,you need to make the gid of each question 1,2.Please refer to:

    CREATE TABLE mytable(  
        id   INTEGER  NOT NULL    
       ,gid  INTEGER  NOT NULL  
       ,col1 VARCHAR(9) NOT NULL  
       ,col2 VARCHAR(10) NOT NULL  
     );  
     INSERT INTO mytable(id,gid,col1,col2) VALUES  
      (1,1,'Name','John')  
     ,(1,2,'Address','Hawaii St')  
     ,(2,1,'Name','Doe')  
     ,(2,2,'Name','msdn st')  
      ,(3,1,'Name','Joy')  
     ,(3,2,'Name','qa st')  
     ,(4,1,'Name','Doe')  
     ,(4,2,'Name','qa st')  
     ,(5,1,'Name','John')  
     ,(5,2,'Name','qa st')  
      
         
     select id,max(case when gid=1 then col2 else null end) name,  
              max(case when gid=2 then col2 else null end) address  
     from mytable  
     group by id  
      
     drop TABLE mytable   
    

    20771-image.png

    Best Regards
    Echo

    No comments