Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

SQL Query to Convert Rows to Columns in SQL Server

  • Last Updated : 16 Dec, 2021

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:

NAMECOLLEGEROLL NUMBERSUBJECTMARKS
ROMYBVP0261150DBMS90
ROMYBVP0261150NETWORKING87
ROMYBVP0261150GRAPHICS95
PUSHKARMSIT0898888DBMS91
PUSHKARMSIT0898888NETWORKING90
PUSHKARMSIT0898888GRAPHICS78

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

NAMECOLLEGEROLL NUMBERDBMSNETWORKINGGRAPHICS
ROMYBVP0261150908795
PUSHKARMSIT0898888919078

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
Recommended Articles
Page :

Start Your Coding Journey Now!