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?

Did you do all of that HTML formatting in your question by hand? If you want to post tabular data and keep the formatting, you could have just highlighted the block and hit the {} button (I'll admit to defeat in terms of being unwilling myself to take out all of that formatting) — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Aug 30, 2013 at 7:47
Are you looking for empty spaces fields in rows of the same group? — Francesco De Lisi
– Francesco De Lisi, Commented Aug 30, 2013 at 7:49
@Damien_The_Unbeliever - I'v noticed that to. +1 on effort to OP. — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Aug 30, 2013 at 7:50
@Mathi If you want to just display result in HTML... Then simply concatenate Subject columns with <br/> tag and resulted dataset table bind with your grid... you will get same result which you need. — Vishal Patel
– Vishal Patel, Commented Dec 6, 2013 at 9:39

Lieven Keersmaekers · Accepted Answer · 2013-08-30 07:47:21Z

4

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.

answered Aug 30, 2013 at 7:47

Lieven Keersmaekers

58.9k15 gold badges117 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

roman Over a year ago

may be you want to select nulls instead of reg_no, student_name ... in rows where you get Subject2, 3 and so on

Lieven Keersmaekers Over a year ago

@RomanPekar - If OP indeed needs it as presented in the question, you are right but how is he ever going to select from that table?An additional key would be required complicating things imo.

Zakk Diaz Over a year ago

I tried UNION but UNION ALL is what did it for me. This saved me a ton, thanks!

Devart · Accepted Answer · 2013-08-30 08:57:09Z

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:

tt2

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.

Giannis Paraskevopoulos · Accepted Answer · 2013-08-30 08:11:00Z

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:

This is the new Fiddle

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

Is not really what OP wants. The pivot part is fine, but he also wants to "clear" all repeating values.
@TimSchmelter, This is not explicitly mentioned anywhere. It is just implied by the requested results...
@TimSchmelter I have edited my answer and provided an additional solution, if you want to check :)
If Reg_No,Student_Name is NULL or nothing means how to get the Subject2,Subject3,Subject4 values..Is it possible to access these values by using Reg_No

Nicolas C. · Accepted Answer · 2013-08-30 07:50:03Z

0

You should look after the PIVOT operator :

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

answered Aug 30, 2013 at 7:50

Nicolas C.

12 bronze badges

Comments

Shailendra Mishra · Accepted Answer · 2020-02-26 08:18:42Z

> DECLARE  @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)
> 
>  select @cols = STUFF((SELECT ',' + QUOTENAME(designation) 
>                      from MyTable
>                      group by designation  
>                      order by designation
>              FOR XML PATH(''), TYPE
>              ).value('.', 'NVARCHAR(MAX)'),1,1,'') 
> 
>  set @query = N'SELECT Row, ' + @cols + N' from 
>               (
>                  select ''SS'' Row, SS AS Value , designation from MyTable
>                  UNION ALL
>                  select  ''AS'' Row, [AS] AS Value , designation from MyTable
>                  UNION ALL 
>                  select  ''Vac'' Row, Vac AS Value , designation from MyTable 
>              ) x
>              pivot 
>              (
>                  max(Value) for designation in (' + @cols + N')
>              ) p '                    
>      exec sp_executesql @query;

For more details: Convert row into column when number of row is not fixed

Collectives™ on Stack Overflow

sql server single row multiple columns into one column

5 Answers 5

3 Comments

Comments

7 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

7 Comments

Comments

Comments

Linked

Related