I have table like this
Reg_No Student_Name Subject1 Subject2 Subject3 Subject4 Total ----------- -------------------- ----------- ----------- ----------- ----------- ----------- 101 Kevin 85 94 78 90 347 102 Andy 75 88 91 78 332
From this I need to create a temp table or table like this:
Reg_No Student_Name Subject Total ----------- -------------------- ----------- ----------- 101 Kevin 85 347 94 78 90 102 Andy 75 332 88 91 78
Is there a way I can do this in SQL Server
?
Advertisement
Answer
Check this Fiddle
;WITH MyCTE AS ( SELECT * FROM ( SELECT Reg_No, [Subject1], [Subject2], [Subject3], [Subject4] FROM Table1 )p UNPIVOT ( Result FOR SubjectName in ([Subject1], [Subject2], [Subject3], [Subject4]) )unpvt ) SELECT T.Reg_No, T.Student_Name, M.SubjectName, M.Result, T.Total FROM Table1 T JOIN MyCTE M ON T.Reg_No = M.Reg_No
If you do want NULL values in the rest, you may try the following:
And here is the code:
;WITH MyCTE AS ( SELECT * FROM ( SELECT Reg_No, [Subject1], [Subject2], [Subject3], [Subject4] FROM Table1 )p UNPIVOT ( Result FOR SubjectName in ([Subject1], [Subject2], [Subject3], [Subject4]) )unpvt ), MyNumberedCTE AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY Reg_No ORDER BY Reg_No,SubjectName) AS RowNum FROM MyCTE ) SELECT T.Reg_No, T.Student_Name, M.SubjectName, M.Result, T.Total FROM MyCTE M LEFT JOIN MyNumberedCTE N ON N.Reg_No = M.Reg_No AND N.SubjectName = M.SubjectName AND N.RowNum=1 LEFT JOIN Table1 T ON T.Reg_No = N.Reg_No