Pivoting in SQL

Problem

I have data like this

and want it to look like this

Solution

Use pivot: 

SELECT * FROM Income
pivot (min([Money]) for Months in ([JAN], [FEB], [MAR])) as Income

Breakdown

First, we've got our select statement which is just grabbing all the records from the table. Next, we're pivoting on the Money column and making new columns from it where the Months are equal to the ones provided (Jan, Feb, and Mar). One sticking point using the pivot operator is that the query has to contain an aggregate function, which is why the min is in there. If there's only one record for each worker for each month, max, min, sum, and avg will all work as aggregators. If not, you need to choose your aggregator based upon the data you want to see.

We don't have to select all columns. 

SELECT * FROM Income
pivot (min([Money]) for Months in ([JAN], [FEB])) as Income

will output

 

We can also flip the statement around

SELECT * FROM Income
pivot (min([Money]) for Workers in ([Bizzy], [Layzie])) as Income

will output

 

Leave any questions/comments/suggestions in the comments.

Add comment

Loading