sql server single row multiple columns into one column

Question

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?

Answer 1

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)

Query #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

Query #2 - OUTER APPLY:

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)

Query Plan:

Query 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 query with OUTER APPLY is faster than ROW_NUMBER solution.

Answer 2

The simplest approach would be to use a UNIONclause

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 the rows that belong to all queries in the union. The UNION operation is different from using joins that combine columns from two tables.

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

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

•The data types must be compatible.