UNPIVOT T-SQL query

• myjobsinus

  SSC Eights!

  Points: 964

  More actions

  September 23, 2015 at 7:16 pm

  #399078

  Hi All,

  I have a table as following . i am writing a query to extract data for a report. i need the output as the #DesiredOutput table. please help!

  -- Actual Table Structure

  CREATE TABLE #StudentInfo (StudentID int,

  FirstName Varchar(50),

  CourseName1 Varchar(50),

  course1Option Varchar(50),

  CourseName2 Varchar(50),

  course2Option Varchar(50),

  CourseName3 Varchar(50),

  course3Option Varchar(50))

  INSERT INTO #StudentInfo VALUES (1,'Mike','English','Single Year','Computers','half year','Mathematics','Multi Year')

  SELECT * FROM #StudentInfo

  --My current query

  SELECT StudentID,

  FirstName,

  CourseName1 AS CourseName,

  course1Option AS CourseOption,

  CourseName2 AS CourseName,

  course2Option AS CourseOption,

  CourseName3 AS CourseName,

  course3Option AS CourseOption

  FROM #StudentInfo

  DROP TABLE #StudentInfo

  --Desired Output

  CREATE TABLE #DesiredOutput (StudentID int,

  FirstName Varchar(50),

  CourseName Varchar(50),

  courseOption Varchar(50))

  INSERT INTO #DesiredOutput VALUES (1,'Mike','English','Single Year')

  INSERT INTO #DesiredOutput VALUES (1,'Mike','Computers','half year')

  INSERT INTO #DesiredOutput VALUES (1,'Mike','Mathematics','Multi Year')

  SELECT * FROM #DesiredOutput

  DROP TABLE #DesiredOutput

  Many Thanks,

  Alvin

• WayneS

  SSC Guru

  Points: 95409

  More actions

  September 23, 2015 at 7:52 pm

  #1829005

  myjobsinus (9/23/2015)

  ---

  Hi All,

  I have a table as following . i am writing a query to extract data for a report. i need the output as the #DesiredOutput table. please help!

  -- Actual Table Structure

  CREATE TABLE #StudentInfo (StudentID int,

  FirstName Varchar(50),

  CourseName1 Varchar(50),

  course1Option Varchar(50),

  CourseName2 Varchar(50),

  course2Option Varchar(50),

  CourseName3 Varchar(50),

  course3Option Varchar(50))

  INSERT INTO #StudentInfo VALUES (1,'Mike','English','Single Year','Computers','half year','Mathematics','Multi Year')

  SELECT * FROM #StudentInfo

  --My current query

  SELECT StudentID,

  FirstName,

  CourseName1 AS CourseName,

  course1Option AS CourseOption,

  CourseName2 AS CourseName,

  course2Option AS CourseOption,

  CourseName3 AS CourseName,

  course3Option AS CourseOption

  FROM #StudentInfo

  DROP TABLE #StudentInfo

  --Desired Output

  CREATE TABLE #DesiredOutput (StudentID int,

  FirstName Varchar(50),

  CourseName Varchar(50),

  courseOption Varchar(50))

  INSERT INTO #DesiredOutput VALUES (1,'Mike','English','Single Year')

  INSERT INTO #DesiredOutput VALUES (1,'Mike','Computers','half year')

  INSERT INTO #DesiredOutput VALUES (1,'Mike','Mathematics','Multi Year')

  SELECT * FROM #DesiredOutput

  DROP TABLE #DesiredOutput

  Many Thanks,

  Alvin

  How about this Alvin?

  SELECT ca.StudentID,

  t.FirstName,

  ca.CourseName,

  ca.CourseOption

  FROM #StudentInfo t

  CROSS APPLY (VALUES (StudentID, CourseName1, course1Option),

  (StudentID, CourseName2, course2Option),

  (StudentID, CourseName3, course3Option)

  )ca(StudentID, CourseName, CourseOption);

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2