Pivot

• Bipinkumar Yadav

  SSC-Addicted

  Points: 443

  More actions

  November 18, 2011 at 5:13 am

  #250775

  Declare @Tmp Table

  (ID int,

  Name varchar(100),

  SUBjectName varchar(100),

  Marks varchar(50))

  INSERT INTO @Tmp

  SELECt '1','A','math' ,'80'

  UNION ALL

  SELECt '1','A','English' ,'80'

  UNION ALL

  SELECt '1','A','Hindi' ,'90'

  UNION ALL

  SELECt '2','B','math' ,'100'

  UNION ALL

  SELECt '2','B','Marathi' ,'80'

  UNION ALL

  SELECt '2','B','English' ,'80'

  UNION ALL

  SELECt '2','B','Hindi' ,'90'

  RESULT

  IDName SUBjectNameMarks

  1A math80

  1A English80

  1A Hindi90

  2B math100

  2B Marathi80

  2B English80

  2B Hindi90

  Pivot result

  1 a Math 80 english 80 hindi 90 Null Null

  2 b Math 100 english 80 hindi 90 Marathi 80

• Ant-Green

  SSC Guru

  Points: 113498

  More actions

  November 18, 2011 at 5:22 am

  #1409815

  maybe this will help

  http://msdn.microsoft.com/en-us/library/ms177410.aspx

  SELECT

  ID, Name, [English] AS English, [Math] AS Math, [Hindi] AS Hindi, [Marathi] AS Marathi

  FROM

  @Tmp

  PIVOT

  (max(marks) for subjectname

  IN ([english],[math],[hindi],[marathi])) as p

  Will give results like this

  IDNameEnglishMathHindiMarathi

  1A808090NULL

  2B801009080

• Bipinkumar Yadav

  SSC-Addicted

  Points: 443

  More actions

  November 18, 2011 at 11:22 am

  #1410097

  Hey friend,

  as per ur query it work with single column.But i wan to Pivot more then column...!!!

  I wan result such format

  1 a Math 80 english 80 hindi 90 Null Null

  2 b Math 100 english 80 hindi 90 Marathi 80