Sql server single row row of columns into one column

Question

I have a 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 a table as follows:

 Reg_No Student_Name Subject Total ----------- -------------------- ----------- ----------- 101 Kevin 85 347 94 78 90 102 Andy 75 332 88 91 78 

Is there a way to do this in SQL Server ?

Answer 1

Check this script

 ;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 want to get NULL values ​​for the rest, you can try the following:

This is a new script.

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 

Answer 2

DDL:

 DECLARE @temp TABLE ( Reg_No INT , Student_Name VARCHAR(20) , Subject1 INT , Subject2 INT , Subject3 INT , Subject4 INT , Total INT ) INSERT INTO @temp (Reg_No, Student_Name, Subject1, Subject2, Subject3, Subject4, Total) VALUES (101, 'Kevin', 85, 94, 78, 90, 347), (102, 'Andy ', 75, 88, 91, 78, 332) 

Request # 1 - ROW_NUMBER:

 SELECT Reg_No = CASE WHEN rn = 1 THEN t.Reg_No END , Student_Name = CASE WHEN rn = 1 THEN t.Student_Name END , t.[Subject] , Total = CASE WHEN rn = 1 THEN t.Total END FROM ( SELECT Reg_No , Student_Name , [Subject] , Total , rn = ROW_NUMBER() OVER (PARTITION BY Reg_No ORDER BY 1/0) FROM @temp UNPIVOT ( [Subject] FOR tt IN (Subject1, Subject2, Subject3, Subject4) ) unpvt ) t 

Request No. 2 - EXTERNAL APPLICATIONS:

 SELECT t.* FROM @temp OUTER APPLY ( VALUES (Reg_No, Student_Name, Subject1, Total), (NULL, NULL, Subject2, NULL), (NULL, NULL, Subject3, NULL), (NULL, NULL, Subject4, NULL) ) t(Reg_No, Student_Name, [Subject], Total) 

Request:

Request Cost

Output:

 Reg_No Student_Name Subject Total ----------- -------------------- ----------- ----------- 101 Kevin 85 347 NULL NULL 94 NULL NULL NULL 78 NULL NULL NULL 90 NULL 102 Andy 75 332 NULL NULL 88 NULL NULL NULL 91 NULL NULL NULL 78 NULL 

PS: In your case, a request with OUTER APPLY faster than ROW_NUMBER .

Answer 3

The easiest approach is to use the UNION clause.

 select Reg_No, Student_Name, Subject1, Total from YourTable union all select Reg_No, Student_Name, Subject2, Total from YourTable union all select Reg_No, Student_Name, Subject3, Total from YourTable union all select Reg_No, Student_Name, Subject3, Total from YourTable 

UNION

Combines the results of two or more queries into a single result set that includes all rows that belong to all queries in the union. The UNION operation is different from using joins joining columns from two tables.

The following are the basic rules for combining result sets of two queries using UNION:

• The number and order of columns must be the same in all queries.

• Data types must be compatible.

Answer 4

You should follow the PIVOT statement:

http://technet.microsoft.com/en-us/library/ms177410(v=sql.100).aspx