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