How to Ttranspose Rows to Columns in SQLite?

Transposing rows to columns in SQLite involves converting data from a row-based format to a column-based format. This operation can be useful for pivoting data, and transforming rows into columns for better analysis or reporting. SQLite does not have a built-in PIVOT function like some other databases but can achieve this result using SQL queries.

In this article, We will learn about How to Transpose Rows to Columns in SQLite by understanding various queries with the help of examples and so on.

How to Transpose Rows to Columns in SQLite?

Transposing rows to columns in SQLite refers to the process of converting data from a row-based format to a column-based format. This transformation is often used to pivot data, making it easier to analyze or present. SQLite does not have a built-in PIVOT function like some other databases. However, we can easily convert from a row-based format to a column-based format with the help of a CASE statement.

To understand How to transpose rows to columns in SQLite we need a table on which we will perform various operations and queries. Here we will consider a table called sales which contains product, month and revenue as Columns.

CREATE TABLE sales (
product TEXT,
month TEXT,
revenue INTEGER
);

INSERT INTO sales (product, month, revenue) VALUES 
('Product A', 'January', 100), 
('Product A', 'February', 150), 
('Product A', 'March', 200), 
('Product B', 'January', 120), 
('Product B', 'February', 180), 
('Product B', 'March', 220);

Output:

Example 1: Product Sales Comparison

Suppose we want to write an SQL query to pivot the data so that each row represents a unique month, with columns showing the maximum revenue for each product (A, B, C) in that month.

SELECT
month,
MAX(CASE WHEN product = 'Product A' THEN revenue ELSE 0 END) AS "Product A",
MAX(CASE WHEN product = 'Product B' THEN revenue END) AS "Product B",
MAX(CASE WHEN product = 'Product C' THEN revenue END) AS "Product C".
FROM sales
GROUP BY month;

Output:

Explanation: This query retrieves data from the "sales" table and pivots it so that each row represents a unique month, with columns showing the maximum revenue for each product (A, B, C) in that month. If a product has no revenue for a specific month, the value is shown as 0. The results are grouped by month.

Example 2: Quarterly Sales Summary

Suppose we want to recall quarterly sales data. So we can do this by having quarterly columns with aggregated sales income for each product.

SELECT
SUBSTR(month, 1, INSTR(month, ' ') - 1) AS quarter,
SUM(CASE WHEN product = 'Product A' THEN revenue END) AS 'Product A',
SUM(CASE WHEN product = 'Product B' THEN revenue ELSE NULL END) AS "Product B"
FROM sales
GROUP BY quarter;

Output:

Explanation: This query fetch the quarter from the "month" column and sums the revenue for "Product A" and "Product B" accordingly. The results are grouped by quarter, showing the total revenue for each product in that quarter.

Example 3: Monthly Sales Trends

Suppose we want to represent the monthly sales trends for each product. The transposing of the data will have each month as a column but sales revenue of each product across the columns.

SELECT
product,
MAX(CASE WHEN month = 'January' THEN revenue END) AS "January",
MAX(CASE WHEN month = 'February' THEN revenue END) AS "February",
MAX(CASE WHEN month='March' THEN revenue END) AS "March"
FROM sales
GROUP BY product

Output:

Explanation: This query uses the CASE statement inside the MAX function to pivot data from rows to columns, showing the maximum revenue for each product (A, B) in each month (January, February, March). The results are grouped by product, displaying the maximum revenue for each month in separate columns.

Conclusion

In conclusion, transposing rows to columns in SQLite is a valuable operation for pivoting data and presenting it in a more structured format. While SQLite lacks a built-in PIVOT function, this transformation can be achieved efficiently using SQL queries and conditional logic, such as the CASE statement. Developers can manipulate and analyze data more effectively, improving the usability and readability of their databases.