UNPIVOT T-SQL query

Transact-SQL
1

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

2 
> SELECT StudentID, FirstName, CourseName, years
> FROM 
>    (SELECT StudentID, FirstName, CourseName1, CourseName2, CourseName3, course1Option, course2Option, course3Option
>    FROM #StudentInfo) p
> UNPIVOT
>    (CourseName FOR Columnname IN  ( CourseName1, CourseName2, CourseName3))AS unpvt
>    UNPIVOT
>    (years FOR Columnname1 IN  ( course1Option, course2Option, course3Option))AS unpvt1

>    WHERE Columnname1 like  '%' + RIGHT(Columnname,1) +'%'
3

even easier:

select FirstName, CourseName, courseOption
from #StudentInfo
cross apply(
    values (CourseName1, course1Option),
              (CourseName2, course2Option),
              (CourseName3, course3Option)
) v(CourseName, courseOption)