Cross Tabulation Using Joins and Aggregate Functions
-- Cross Tabulation Using Joins and Aggregate Functions
SELECT
e.department,
SUM(CASE WHEN e.employee_id = 1 THEN e.salary END) AS Employee1,
SUM(CASE WHEN e.employee_id = 2 THEN e.salary END) AS Employee2,
SUM(CASE WHEN e.employee_id = 3 THEN e.salary END) AS Employee3,
SUM(CASE WHEN e.employee_id = 4 THEN e.salary END) AS Employee4,
SUM(CASE WHEN e.employee_id = 5 THEN e.salary END) AS Employee5
FROM
employees e
GROUP BY
e.department;
Explanation: This query performs cross-tabulation, displaying total salaries for specific employees (Employee1 to Employee5) within their respective departments. Each row represents a department, and the columns show the aggregated salaries for the specified employees in those departments.
How to Return Pivot Table Output in MySQLPivot MySQL
Pivoting a table in MySQL involves transforming rows into columns, which can be particularly useful for data analysis and reporting. While MySQL does not have a native PIVOT function like some other database systems, you can achieve pivoting using conditional aggregation with the CASE statement.
So, In this article, we will explore how can I return pivot table output in MySQL, using the syntax, methods, methods, and some examples that will help to understand the process.
Pivot MySQL
Pivoting a table allows you to reorganize and summarize data, making it easier to analyze. In MySQL, this is accomplished by using the CASE statement within an aggregate function, typically SUM, to create conditional columns. These columns represent the pivoted values, and the result is a transformed dataset where rows become columns.
- Dynamic Columns with GROUP_CONCAT
- Conditional Aggregation with CASE Statements
- Cross Tabulation Using Joins and Aggregate Functions
Syntax:
The general syntax for pivoting a table in MySQL involves using the CASE statement within an aggregate function, often SUM, and grouping the results by the remaining non-pivoted columns.
SELECT
non_pivoted_column,
SUM(CASE WHEN pivoted_column = ‘value1’ THEN aggregate_column END) AS value1,
SUM(CASE WHEN pivoted_column = ‘value2’ THEN aggregate_column END) AS value2,
— Additional pivoted columns as needed
FROM
your_table
GROUP BY
non_pivoted_column;