Pivot or row to column

• dva2007

  SSCertifiable

  Points: 7707

  More actions

  July 13, 2011 at 3:55 am

  #244141

  DECLARE @tblStudents table ( StudentID INT,

  StudentName VARCHAR(100), CollegeName VARCHAR(100))

  INSERT INTO @tblStudents

  SELECT '1','question1','answer1'

  UNION ALL SELECT '1','question1','answer2'

  UNION ALL SELECT '1','question1','answer3'

  UNION ALL SELECT '1','question1','answer4'

  UNION ALL SELECT '2','question1','answer1'

  UNION ALL SELECT '2','question1','answer2'

  UNION ALL SELECT '2','question1','answer3'

  UNION ALL SELECT '2','question1','answer4'

  SELECT * FROM @tblStudents

  the output should be

  id question1 question2 question2 question4

  1answer1 answer2 answer3 answer4

  2answer1 answer2 answer3 answer4

  What is the best way to do this?

  thanks.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 13, 2011 at 5:40 am

  #1352077

  dva2007 (7/13/2011)

  ---

  ...What is the best way to do this?...

  Ensure that your table structure is correct and that it is feasible to obtain the results you expect from the data you provide.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  July 14, 2011 at 10:22 am

  #1352951

  You probably meant to have the question number change for each line along with the answer number, in which case this would produce your output. However, you are dealing with aggregate operations in your result set. What is the case that should occur if there are multiple answers for a given question by a student?

  DECLARE @tblStudents table ( StudentID INT,

  StudentName VARCHAR(100), CollegeName VARCHAR(100))

  INSERT INTO @tblStudents

  SELECT '1','question1','answer1'

  UNION ALL SELECT '1','question2','answer2'

  UNION ALL SELECT '1','question3','answer3'

  UNION ALL SELECT '1','question4','answer4'

  UNION ALL SELECT '2','question1','answer1'

  UNION ALL SELECT '2','question2','answer2'

  UNION ALL SELECT '2','question3','answer3'

  UNION ALL SELECT '2','question4','answer4'

  SELECT

  StudentID,

  MAX(CASE WHEN StudentName = 'question1' THEN CollegeName ELSE NULL END) AS 'question1',

  MAX(CASE WHEN StudentName = 'question2' THEN CollegeName ELSE NULL END) AS 'question2',

  MAX(CASE WHEN StudentName = 'question3' THEN CollegeName ELSE NULL END) AS 'question3',

  MAX(CASE WHEN StudentName = 'question4' THEN CollegeName ELSE NULL END) AS 'question4'

  FROM @tblStudents

  GROUP BY StudentID