Pivoting your data from long format (where values repeat in the first column) to wide format (where values do NOT repeat in the first column) comes in handy when you need to turn unique values in a column into individual columns.
You can certainly use the PIVOT function to do so, but not all warehouses support PIVOT since it is a newer function, and its syntax can be hard to remember.
Enter the trusty CASE statement to get the job done, no matter what warehouse you’re working in!
Pivoting using CASE aggregates by the column for which you want to get unique values, and then defines the scenarios that would classify a given record to each of those values.
In cases that do not satisfy a certain category’s CASE criteria, an ‘ELSE NULL’ is used so that the result of the aggregation is not affected by a record that does belong in the category.
In the below example, we are pivoting daily sales data into a report that will show us the total sales that have been brought in by each salesperson of our company.
SELECT salesperson
, SUM(CASE WHEN DATEPART(month, date) = 1 THEN revenue ELSE NULL END) AS jan_rev
, SUM(CASE WHEN DATEPART(month, date) = 2 THEN revenue ELSE NULL END) AS feb_rev
SUM(CASE WHEN DATEPART(month, date) = 12 THEN revenue ELSE NULL END) AS dec_rev
FROM FROM myschema.sales GROUP BY salesperson
Important Notice for college students
If you’re a college student and have skills in programming languages, Want to earn through blogging? Mail us at geekycomail@gmail.com
For more Programming related blogs Visit Us Geekycodes. Follow us on Instagram.