Pivot table

Question

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

Answer 1

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 

Answer 2

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   

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

Answer 3

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   

Best Regards
Echo