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:

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

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. 

romy421kumari

Follow

Improve

Article Tags :

• SQL
• SQL-Server
• SQL-Query