Examples of Efficient Row to Column Conversion in SQL
Example 1: Pivoting Sales Data for Products by Month
Let’s say we have a table called “Sales” with the following data:
“We can create the ‘Sales‘ table using the following SQL code, which defines the table structure with columns such as ‘Product,’ ‘Month,’ and ‘Sales,’ specifying appropriate data types for each column to store information about sales’ marks in different subjects.”
CREATE TABLE Sales (
Product VARCHAR(50),
Month VARCHAR(3),
Sales INT
);
INSERT INTO Sales (Product, Month, Sales)
VALUES
('A', 'Jan', 100),
('A', 'Feb', 200),
('A', 'Mar', 300),
('B', 'Jan', 150),
('B', 'Feb', 250),
('B', 'Mar', 350);
Sales table:
We want to pivot this data to show the total sales for each product in each month. The query would look like this:
SELECT Product, Jan, Feb, Mar
FROM sales
PIVOT
(
SUM(Sales)
FOR Month IN (Jan, Feb, Mar)
) AS SalesByMonth;
Output:
Output:
The output of this SQL query presents a pivoted view of sales data for each product across different months. The columns represent months (Jan, Feb, Mar), and the corresponding values indicate the total sales for each product in the respective months. This transformation facilitates a clearer analysis of sales performance over time.
Example 2: Pivoting Student Marks by Subject
Let’s take another example where we have a table called “students” with the following data:
“We can create the ‘Students‘ table using the following SQL code, which defines the table structure with columns such as ‘Student,’ ‘Subject,’ and ‘Marks,’ specifying appropriate data types for each column to store information about students’ marks in different subjects.”
CREATE TABLE Students (
Student VARCHAR(50),
Subject VARCHAR(50),
Marks INT
);
INSERT INTO Students (Student, Subject, Marks)
VALUES
('John', 'Math', 90),
('John', 'Science', 80),
('John', 'English', 95),
('Jane', 'Math', 85),
('Jane', 'Science', 75),
('Jane', 'English', 90);
We want to pivot this data to show the marks for each subject for each student. The query would look like this:
SELECT Student, Math, Science, English
FROM students
PIVOT
(
MAX(Marks)
FOR Subject IN (Math, Science, English)
) AS MarksBySubject;
Output:
Explanation:
The output of this SQL query presents a pivoted view of student marks data, showcasing the scores for each subject (Math, Science, English) across different students. Each row represents a student, and the columns display the maximum marks achieved in the respective subjects. This pivot simplifies the analysis of individual student performance in different subjects.
How to Efficiently Convert Rows to Columns in SQL?
In SQL, rows and columns are the fundamental building blocks of a database. Rows represent individual records, while columns represent the attributes or characteristics of those records. However, there may be instances where we need to convert rows to columns in order to better analyze and manipulate data. This process is known as “pivoting” and can be done efficiently using SQL. In this article, we will explore the concept of converting rows to columns in SQL and provide step-by-step instructions on how to do it.
Contact Us