locked
Transpose Row Data to column RRS feed

  • Question

  • User-1495932509 posted

    Hello my respected Programmers and consultants,

    I have an issue with this query , i want make a query that will transpose my data from row to column, my data is as follows:

    ADMISSION NO

    FULL NAME

    SUBJECT

    GRADE

    3886

    ALIYU USMAN

    MATHEMATICS

    A

    3886

    ALIYU USMAN

    ENGLISH

    B

    3886

    ALIYU USMAN

    BIOLOGY

    B

    3887

    JOHN INNOCENT

    MATHEMATICS

    A

    3887

    JOHN INNOCENT

    ENGLISH

    A

    3887

    JOHN INNOCENT

    BIOLOGY

    A

    and i want to display it like the following table.

    AMISSION NO

    FULL NAME

    MATHEMATICS

    ENGLISH

    BIOLOGY

    3886

    ALIYU USMAN

    A

    B

    B

    3887

    JOHN INNOCENT

    A

    A

    A

    Please i seriously need you help.

    Than you

    Tuesday, November 17, 2015 1:26 AM

Answers

  • User-62323503 posted

    You need to implement pivot for it. If Subject are fixed you can implement static pivot other wise you have to use dynamic pivot

    Refer below post:

    http://www.itdeveloperzone.com/2011/11/dynamic-pivot-in-sql-server.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 17, 2015 5:13 AM
  • User77042963 posted
    create table test (ADMISSIONNO int,FULLNAME varchar(50), SUBJECT varchar(50), GRADE Char(1))
    Insert into test values 
    
    (3886,'ALIYU USMAN','MATHEMATICS','A'),
    (3886,'ALIYU USMAN','ENGLISH','B'),
    (3886,'ALIYU USMAN','BIOLOGY','B'),
    
    (3887,'JOHN INNOCENT','MATHEMATICS','A'),
    (3887,'JOHN INNOCENT','ENGLISH','A'),
    (3887,'JOHN INNOCENT','BIOLOGY','A')
     
     ---Case
     Select ADMISSIONNO
     ,Max(Case WHEN Subject='MATHEMATICS' Then Grade End) MATHEMATICS 
     ,Max(Case WHEN Subject='ENGLISH' Then Grade End) ENGLISH 
     ,Max(Case WHEN Subject='BIOLOGY' Then Grade End) BIOLOGY 
     from test
     Group by ADMISSIONNO
    
    --Pivot
     Select ADMISSIONNO,MATHEMATICS, ENGLISH , BIOLOGY 
     from test
     Pivot (Max(Grade) For SubJect IN ([MATHEMATICS],[ENGLISH],[BIOLOGY])) pvt
     
    
    --Dynamic Pivot
    
    DECLARE @cols  AS NVARCHAR(2000), @sql  AS NVARCHAR(4000)
     
    SELECT @cols   = STUFF((select DISTINCT ', ' + quotename(SubJect  ,']') 
     FROM test
     FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '') 
     --Print @cols   
      
    Set @sql=N'SELECT ADMISSIONNO, '+ @cols   +  ' FROM  test PIVOT (Max(Grade) for Subject IN ('+ @cols +')) pvt'
      
    exec sp_executesql @sql;
      
    
    
     drop table test

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 17, 2015 10:20 AM

All replies

  • Tuesday, November 17, 2015 1:49 AM
  • User-62323503 posted

    You need to implement pivot for it. If Subject are fixed you can implement static pivot other wise you have to use dynamic pivot

    Refer below post:

    http://www.itdeveloperzone.com/2011/11/dynamic-pivot-in-sql-server.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 17, 2015 5:13 AM
  • User77042963 posted
    create table test (ADMISSIONNO int,FULLNAME varchar(50), SUBJECT varchar(50), GRADE Char(1))
    Insert into test values 
    
    (3886,'ALIYU USMAN','MATHEMATICS','A'),
    (3886,'ALIYU USMAN','ENGLISH','B'),
    (3886,'ALIYU USMAN','BIOLOGY','B'),
    
    (3887,'JOHN INNOCENT','MATHEMATICS','A'),
    (3887,'JOHN INNOCENT','ENGLISH','A'),
    (3887,'JOHN INNOCENT','BIOLOGY','A')
     
     ---Case
     Select ADMISSIONNO
     ,Max(Case WHEN Subject='MATHEMATICS' Then Grade End) MATHEMATICS 
     ,Max(Case WHEN Subject='ENGLISH' Then Grade End) ENGLISH 
     ,Max(Case WHEN Subject='BIOLOGY' Then Grade End) BIOLOGY 
     from test
     Group by ADMISSIONNO
    
    --Pivot
     Select ADMISSIONNO,MATHEMATICS, ENGLISH , BIOLOGY 
     from test
     Pivot (Max(Grade) For SubJect IN ([MATHEMATICS],[ENGLISH],[BIOLOGY])) pvt
     
    
    --Dynamic Pivot
    
    DECLARE @cols  AS NVARCHAR(2000), @sql  AS NVARCHAR(4000)
     
    SELECT @cols   = STUFF((select DISTINCT ', ' + quotename(SubJect  ,']') 
     FROM test
     FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '') 
     --Print @cols   
      
    Set @sql=N'SELECT ADMISSIONNO, '+ @cols   +  ' FROM  test PIVOT (Max(Grade) for Subject IN ('+ @cols +')) pvt'
      
    exec sp_executesql @sql;
      
    
    
     drop table test

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 17, 2015 10:20 AM
  • User-1495932509 posted

    WOW IT WORKS FOR ME 

    BIG THANK YOU , YOU GUYS ARE THE BEST EVER

    THANK YOU 

    Wednesday, November 18, 2015 3:11 PM