Converting rows to columns in SQL Server is a common requirement when working with relational databases.
This process is known as "pivoting" and can be achieved using various techniques.
One such technique is using a Common Table Expression (CTE) in SQL Server.
A CTE is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It is similar to a derived table, but with some additional features, such as recursion.
To pivot rows into columns using a CTE, you can follow the steps below:
1. Define the CTE
Begin by defining the CTE that will hold the original data. This CTE should include all the necessary columns required for the pivot operation.
For example, suppose we have a table named "Sales" that contains the following data:
| Product | Year | Quarter | Sales |
|---|---|---|---|
| A | 2020 | Q1 | 100 |
| A | 2020 | Q2 | 200 |
| A | 2020 | Q3 | 150 |
| A | 2020 | Q4 | 300 |
| B | 2020 | Q1 | 50 |
| B | 2020 | Q2 | 100 |
| B | 2020 | Q3 | 75 |
| B | 2020 | Q4 | 150 |
create table Sales
(
Product varchar(2),
Year int,
Quarter varchar(2),
Sales int
);
insert into Sales(Product, Year, Quarter, Sales) values
('A','2020','Q1',100), ('A','2020','Q2',200),
('A','2020','Q3',150), ('A','2020','Q4',300),
('B','2020','Q1',50), ('B','2020','Q2',100),
('B','2020','Q3',75), ('B','2020','Q4',150);
We can define a CTE as follows:
WITH CTE_Sales AS (
SELECT Product, Year, Quarter, Sales
FROM Sales
)
2. Define the Pivot Query
Next, define the pivot query using the CTE. This query will transform the rows into columns based on a defined set of values.
For example, we can pivot the data by the year and quarter as follows:
SELECT Product,
SUM(CASE WHEN Year = 2020 AND Quarter = 'Q1' THEN Sales ELSE 0 END) AS Q1_2020,
SUM(CASE WHEN Year = 2020 AND Quarter = 'Q2' THEN Sales ELSE 0 END) AS Q2_2020,
SUM(CASE WHEN Year = 2020 AND Quarter = 'Q3' THEN Sales ELSE 0 END) AS Q3_2020,
SUM(CASE WHEN Year = 2020 AND Quarter = 'Q4' THEN Sales ELSE 0 END) AS Q4_2020
FROM CTE_Sales
GROUP BY Product
| Product | Q1_2020 | Q2_2020 | Q3_2020 | Q4_2020 |
|---|---|---|---|---|
| A | 100 | 200 | 150 | 300 |
| B | 50 | 100 | 75 | 150 |
In this example, we used the SUM function with a CASE statement to pivot the data. The CASE statement checks for the year and quarter values and returns the Sales amount if the conditions are met; otherwise, it returns 0. The SUM function then aggregates the Sales amount for each quarter and year combination.
In summary, using a CTE in SQL Server can be an effective way to pivot rows into columns. By following the above steps, you can easily transform your data into a more readable format for reporting and analysis purposes.