Pivoting Problem

• gaurav-404321

  SSCommitted

  Points: 1593

  More actions

  July 18, 2011 at 5:23 am

  #389998

  I have a table. I want to pivot this table rows. Attached is the excel containing the input table format and output required. I have written only two rows in output table. But i have more than 2000 rows in this table which needs to be converted in desired output. Please help.

  Following is the query to create the input table and data:

  CREATE TABLE InputTable (RegistrationNumber VARCHAR(50),RoomNumber VARCHAR(20),[11] CHAR(1),[12] CHAR(1),[13] CHAR(1),[14] CHAR(1),[15] CHAR(1),

  [16] CHAR(1),CreatedBy INT)

  INSERT INTO InputTable VALUES ('11005454','26- 404 X','P','P','P','P','P','A',13316)

  INSERT INTO InputTable VALUES ('3020070014','26- 405 X','P','P','A','P','P','P',13316)

• Mike01

  SSChampion

  Points: 11412

  More actions

  July 18, 2011 at 6:31 am

  #1354191

  You didn't specify where the CourseCode is coming from, but here's an unpivot statement that will work

  SELECT RegistrationNumber, LectureNumber, RoomNumber, AttendanceCode, CreatedBy

  FROM

  (SELECT RegistrationNumber, [11], [12], [13], [14], [15], [16], RoomNumber,CreatedBy

  FROM InputTable) p

  UNPIVOT

  (AttendanceCode FOR LectureNumber IN

  ([11],[12],[13],[14],[15],[16])

  )AS unpvt;

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/