Open In App

SQL Query to Convert Rows to Columns in SQL Server

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

In this article we will see, how to convert Rows to Column in SQL Server. In a table where many columns have the have same data for many entries in the table, it is advisable to convert the rows to column. This will help to reduce the table and make the table more readable.

For example, Suppose we have a table given below:

NAME COLLEGE ROLL NUMBER SUBJECT MARKS
ROMY BVP 0261150 DBMS 90
ROMY BVP 0261150 NETWORKING 87
ROMY BVP 0261150 GRAPHICS 95
PUSHKAR MSIT 0898888 DBMS 91
PUSHKAR MSIT 0898888 NETWORKING 90
PUSHKAR MSIT 0898888 GRAPHICS 78

It is better if we store the data of this table as:

NAME COLLEGE ROLL NUMBER DBMS NETWORKING GRAPHICS
ROMY BVP 0261150 90 87 95
PUSHKAR MSIT 0898888 91 90 78

We can convert rows into column using PIVOT function in SQL.

Syntax:

SELECT (ColumnNames)  
FROM (TableName)  
PIVOT
(  
  AggregateFunction(ColumnToBeAggregated)
  FOR PivotColumn IN (PivotColumnValues)
) AS (Alias);                                   
 //Alias is a temporary name for a table
  

For the purpose of the demonstration, we will be creating a demo_table in a database called “geeks“.

Step 1: Creating the Database

Use the below SQL statement to create a database called geeks.

Query:

CREATE DATABASE geeks;

Step 2: Using the Database

Use the below SQL statement to switch the database context to geeks.

Query:

USE geeks;

Step 3: Table definition

We have the following demo_table in our geek’s database.

Query:

CREATE TABLE demo_table(
NAME varchar(30),
COLLEGE varchar(30),
EXAM_DATE DATE,
SUBJECTS varchar(30),
MARKS int);

Step 4: Insert data into the table

Query:

INSERT INTO demo_table VALUES ('ROMY', 'BVCOE', 
'12-OCT-2021', 'DBMS', 90),
('ROMY', 'BVCOE', '12-OCT-2021', 'NETWORKING', 90),
('ROMY', 'BVCOE', '12-OCT-2021', 'GRAPHICS', 100),
('ROMY', 'BVCOE', '12-OCT-2021', 'CHEMISTRY', 98),
('ROMY', 'BVCOE', '12-OCT-2021', 'MATHEMATICS', 78),
('PUSHKAR', 'MSIT', '14-OCT-2021', 'NETWORKING' , 97),
('PUSHKAR', 'MSIT', '14-OCT-2021', 'GRAPHICS', 98),
('PUSHKAR', 'MSIT', '14-OCT-2021', 'CHEMISTRY', 79),
('PUSHKAR', 'MSIT', '14-OCT-2021', 'MATHEMATICS', 79),
('PUSHKAR', 'MSIT', '14-OCT-2021', 'DBMS', 79);

Step 5: See the content of the table

Use the below command to see the content of the demo_table:

Query:

SELECT * FROM demo_table;

Output:

Step 6: Using pivot function in order to convert row into column.

Query:

SELECT * FROM demo_table  
 PIVOT
(AVG(MARKS) FOR SUBJECTS IN (DBMS,NETWORKING, 
GRAPHICS, CHEMISTRY, MATHEMATICS)) AS PivotTable;

We have used AVERAGE aggregate function because average of one value is the value itself.

Output:

 We can see that rows get transformed to column. 


Last Updated : 16 Dec, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads