SQL Query to Convert Rows to Columns in SQL Server

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. 

My Personal Notes arrow_drop_up

Save