Pivot Table In SQL

Galaxy Glossary

How can you transform rows into columns in a SQL table?

Pivot tables in SQL allow you to rotate rows into columns, making data easier to analyze and present in a tabular format. This is particularly useful when you need to aggregate data based on different categories.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Description

Pivot tables, while not a standard SQL command, are a powerful technique for transforming data. They are commonly used to summarize data in a way that's easier to read and understand. Instead of having multiple rows representing the same category, a pivot table aggregates those rows into columns. This is especially helpful when you need to compare values across different categories. For example, imagine you have sales data with columns for product, region, and sales amount. A pivot table could show total sales for each product in each region. This transformation is often achieved using aggregate functions (like SUM, AVG, COUNT) in conjunction with a GROUP BY clause and a CASE statement or similar conditional logic. The resulting table is often more concise and easier to interpret than the original data. Pivot tables are not a built-in SQL feature, but can be achieved through various techniques, including using conditional aggregation or subqueries. The specific approach depends on the database system you are using.

Why Pivot Table In SQL is important

Pivot tables are crucial for data analysis and reporting. They allow you to quickly summarize and compare data across different categories, making it easier to identify trends and patterns. This is essential for business decisions and informed strategic planning.

Pivot Table In SQL Example Usage


-- Sample table (Sales)
CREATE TABLE Sales (
    Product VARCHAR(50),
    Region VARCHAR(50),
    SalesAmount INT
);

INSERT INTO Sales (Product, Region, SalesAmount) VALUES
('Laptop', 'North', 1000),
('Laptop', 'South', 1500),
('Tablet', 'North', 500),
('Tablet', 'South', 700),
('Phone', 'North', 800),
('Phone', 'South', 1200);

-- Pivot query
SELECT
    Product,
    SUM(CASE WHEN Region = 'North' THEN SalesAmount ELSE 0 END) AS NorthSales,
    SUM(CASE WHEN Region = 'South' THEN SalesAmount ELSE 0 END) AS SouthSales
FROM
    Sales
GROUP BY
    Product;

Common Mistakes

Want to learn about other SQL terms?

With Cte In SQL

Common Table Expressions (CTEs) are temporary, named result sets defined within a single SQL statement. They enhance query readability and reusability by breaking down complex queries into smaller, manageable parts.

Where Vs Having SQL

WHERE and HAVING are both used for filtering data in SQL, but they operate on different levels. WHERE filters rows before any aggregation, while HAVING filters groups after aggregation.

Windows Functions In SQL

Window functions in SQL perform calculations over a set of rows related to the current row, unlike aggregate functions which summarize data across all rows. They are powerful tools for analyzing data within a specific context.

Company
BlogFeaturesPricingAboutFeature Requests
Resources
DocsSupportSecurity
Explore
Product TourData Job BoardRecruitersConsultantsStartup ToolsMemes
Learn
Learn SQLBeginner ResourcesData ToolsSQL Interview PrepCommon ErrorsSQL OptimizationSQL In Other LanguagesGlossary
Comparisons
DatagripTablePlusDBeaverBeekeeper StudiopgAdminPosticoCursor
HexBrieferPopSQLSherloqBlazeSQLSweep AIWaii AIOuterbase
© 2025 Galaxy Software Inc.
hello@getgalaxy.io
Reddit IconProduct Hunt IconTwitter / X IconLinkedIn Icon