SQL query to convert column to row

During the creation of table maximum time we only consider the purpose and efficiency of the table. All time we don’t think about the reporting. Some times we may need to convert the columns in to row or vice versa. We can do that in different ways. This article provides a solution to convert the columns of a table into row in SQL query.For example consider a table “Marks” which has six columns and some data:

StudentID | Subject1 | Subject2 | Subject3 | Subject4 | subject5 | Subject6
1    |    10    |    12    |    13    |    9     |    15    |    16
2    |    12    |    14    |    10    |    16    |    19    |    13
3    |    10    |    12    |    13    |    18    |    12    |    15
4    |    14    |    12    |    13    |    7     |    11    |    17

And we want to generate the following output in a single SQL query:

StudentID | Subjects | Marks
1   |  subject1   |  10     
1   |  subject2   |  12     
1   |  subject3   |  13     
1   |  subject4   |  9     
1   |  subject5   |  15     
1   |  subject6   |  16     
2   |  subject1   |  12     
2   |  subject2   |  14     
2   |  subject3   |  10     
2   |  subject4   |  16     
2   |  subject5   |  19     
2   |  subject6   |  13   
3   |  subject1   |  10     
3   |  subject2   |  12     
3   |  subject3   |  13     
3   |  subject4   |  18     
3   |  subject5   |  12     
3   |  subject6   |  15  
4   |  subject1   |  14     
4   |  subject2   |  12     
4   |  subject3   |  13     
4   |  subject4   |  7     
4   |  subject5   |  11     
4   |  subject6   |  17   

At first we need to create the “Marks” table and insert some sample data in to our database. Write down the following SQL scripts and execute its to create the table. It will insert some sample data also.

CREATE TABLE [dbo].[Marks](
    [StudentID] [int] NULL,
    [Subject1] [int] NULL,
    [Subject2] [int] NULL,
    [Subject3] [int] NULL,
    [Subject4] [int] NULL,
    [Subject5] [int] NULL,
    [Subject6] [int] NULL
) ON [PRIMARY]

INSERT [dbo].[Marks] ([StudentID], [Subject1], [Subject2], [Subject3], [Subject4], [Subject5], [Subject6]) VALUES (1, 10, 12, 13, 9, 16, 16)
INSERT [dbo].[Marks] ([StudentID], [Subject1], [Subject2], [Subject3], [Subject4], [Subject5], [Subject6]) VALUES (2, 12, 14, 10, 16, 19, 13)
INSERT [dbo].[Marks] ([StudentID], [Subject1], [Subject2], [Subject3], [Subject4], [Subject5], [Subject6]) VALUES (3, 10, 12, 13, 18, 12, 15)
INSERT [dbo].[Marks] ([StudentID], [Subject1], [Subject2], [Subject3], [Subject4], [Subject5], [Subject6]) VALUES (4, 14, 12, 13, 7, 11, 17)

SQL Query to Convert Column to Row
To convert column to row write the following SQL query:

SELECT     StudentID, 'subject1' AS Subjects, Subject1 AS Marks FROM    Marks
    UNION ALL
SELECT     StudentID, 'subject2' AS Subjects, Subject2  FROM    Marks
    UNION ALL
SELECT     StudentID, 'subject3' AS Subjects, Subject3  FROM    Marks
    UNION ALL
SELECT     StudentID, 'subject4' AS Subjects, Subject4  FROM    Marks
    UNION ALL
SELECT     StudentID, 'subject5' AS Subjects, Subject5 FROM    Marks
    UNION ALL
SELECT     StudentID, 'subject6' AS Subjects, Subject6 FROM    Marks

UNION ALL operator is used.

Output:
If we run/execute the above SQL query it will return our required output.

StudentID    Subjects    Marks
1    subject1    10
2    subject1    12
3    subject1    10
4    subject1    14
1    subject2    12
2    subject2    14
3    subject2    12
4    subject2    12
1    subject3    13
2    subject3    10
3    subject3    13
4    subject3    13
1    subject4    9
2    subject4    16
3    subject4    18
4    subject4    7
1    subject5    16
2    subject5    19
3    subject5    12
4    subject5    11
1    subject6    16
2    subject6    13
3    subject6    15
4    subject6    17

In this way we can convert column to row of a table by using SQL query.