Convert Rows to columns using "Pivot" in SQL Server

Converting Rows to Columns using 'Pivot' in SQL Server 🔄

Have you ever found yourself struggling to convert rows to columns in SQL Server? Don't worry, you're not alone! Many developers face this challenge when trying to transform data in their queries. In this blog post, we'll explore how to use the 'Pivot' feature in SQL Server to achieve this task effortlessly.

The Problem 🤔

Let's start by understanding the problem at hand. Suppose we have a temporary table with three columns: Store, Week, and xCount. The Store number and Week number columns contain static and dynamic values, respectively. We want to present the data in a pivot table with Store numbers on the left-hand side and weeks on the top.

Here's an example of the source table:

Store     Week     xCount
-------   ----     ------
102       1        96
101       1        138
105       1        37
109       1        59
101       2        282
102       2        212
105       2        78
109       2        97
105       3        60
102       3        123
101       3        220
109       3        87

And this is how we want the pivot table to look like:

Store     1        2        3      4      5      6....
----- 
101       138      282      220
102       96       212      123
105       37               
109

The Solution 💡

To achieve the desired pivot table, we can utilize the 'Pivot' feature in SQL Server. This feature allows us to rotate rows into columns based on specified criteria.

Here's an example query that uses the 'Pivot' feature to convert the rows into columns:

SELECT *
FROM (
    SELECT Store, Week, xCount
    FROM YourTemporaryTable
) AS SourceTable
PIVOT (
    SUM(xCount)
    FOR Week IN ([1], [2], [3], [4], [5], [6]...)
) AS PivotTable
ORDER BY Store;

In this query, we first select the Store, Week, and xCount columns from our temporary table. Then, we apply the 'Pivot' function and specify the column to aggregate (xCount in this case) and the column to pivot on (Week). We also define the desired weeks in the 'FOR Week IN' clause.

Finally, we alias the subquery as SourceTable and the resulting pivot table as PivotTable. Sorting by Store in ascending order gives us the desired output.

Call-to-Action 📢

Congratulations, you now know how to convert rows to columns using the 'Pivot' feature in SQL Server! 🎉

Next time you encounter a similar data transformation challenge, don't panic. Utilize the power of 'Pivot' and impress your colleagues with your newfound SQL skills.

Leave a comment below and let us know if this blog post helped you or if you have any further questions. Don't forget to share this article with your fellow developers who might find it useful. Happy pivoting! 😄