I've got table of data here. But I want to convert the rows to columns.
Luckily this is easy with SQL.
It's time to do the PIVOT!
I'm watching my figure so I'm tracking how much I eat. For
everything I consume I'm writing down: what it was, the date and time, and how
many calories. This gives a big list of what I've eaten. But it can be hard to
see if I'm staying within like daily calorie limits. To overcome this we can
group by day and sum up the calories. This is better. But if I regularly
overeat on Saturday this can be tricky to spot. Particularly if I'm on one of
those diets that allows me to eat more at the weekends. To help with this what
we really want is a table with weeks down the side and days across the top.
This makes it easier to see if there's any days where I just can't resist
Stuffing my face!
Luckily this is easy with SQL. First there's a couple of
things we need. The day of the week which you can extract from a date using to_char
and the first day of each week; which you can get with trunc.
We want one row per week. So group by the first day of the week to do this. Then we need
seven calorie counts; one for each day. But if you just add them up you get the
weekly total for each day! To fix this inside each sum check if the current day
is Monday, Tuesday, Wednesday, and so on as appropriate. If it is return the calories
for that day and null otherwise. So now we've converted our daily rows to weekly
columns. But the SQL is kind of messy. And in the sums we have seven similar
but not quite the same expressions making mistakes likely.
Luckily this got a whole lot easier with the pivot clause.
To use this start
the query again, sticking pivot after the from clause. In here you need to state
three things. Firstly the values you want to go in the new columns. Pivoting
normally combines many rows into one. So this must be an aggregate
function. Here it's the sum of the calories. Next you define the pivoting
column which is the to_char of the date. And last a list of all the values from
that which become columns in your results. Here
that's the days of the week. But do this and you're gonna run into a problem:
You can't use a function as the pivoting column! Only a real column. But we don't
have day of week in the table. To overcome this you need a subquery that does the
conversion. Then pivots the results of that. As we also want the start of the
week we can put that in here too. But now we've got another problem: the output has
one row for each week and food. That's not really what we want.
We just wanted one row for each week. So why is that?
Well Oracle database adds an
implicit group by to your pivot. Any columns in your table or subquery that
aren't in the pivot become part of this grouping. We've selected food but not
included in the pivot. So it becomes a column in the results. To fix this remove
the unwanted columns from the subquery. Or you could add them to the pivot. For
example we could also have columns showing a list of all the things I've
eaten each day. Listagg is a handy function that does this, combining the
rows into one comma separated string.
Do this and it's clear: I'm eating too much
at the weekend. But am i loading up on nice healthy fruit?
Or stuffing my fat face with cake?
To help figure this out it would be useful to have the daily
calorie total for each food. To do this we can write out all the foods in the in
list. But this list is likely to be large and change slightly from week to week.
You could list out the foods I eat regularly. But this could miss last
night's late-night nacho binge.
Really you want this list to be dynamic so
the columns change based on the data selected. If this was a where clause you
could do this using a subquery. Sadly this doesn't work with pivot. You've got
to explicitly list out the values. Unless that is you're brave enough to play with
XML.
By doing an XML pivot you get your results in this format. And you can
replace the in list with a subquery. Or use the any keyword which dynamically
generates the values. This seems easy but instead of separate columns for each
value you just get one. This contains an XML document. Each value is in its own
item tag. So if you want them to become columns you still need to know what the
values are and get good at XML manipulation!
So this can be a problem if you want the column list to be dynamic.
There are other ways to overcome this
problem too. But each comes with their own drawbacks. In general have you want
the column list to change regularly for your pivot you probably need to rethink
your requirements! For now let's just list out some of the common foods.
But if that's all we do we get the totals for every day. We only wanted to see this for
Saturday. So we need a where clause. Pivot sits between the from and where clauses.
So if you want to filter the results place this at the end. Or you could
restrict the rows to Saturdays in the subquery. This is a good idea as it
ensures you access as few rows as possible as soon as possible.
Filtering early like this is a great principle to follow in SQL because the smaller
your dataset is the faster your queries are likely to be.
Do this and it's clear:
I'm eating too much cake. But am I having small snacks throughout the day? Or one
monster cake orgy? If we started with this table with foods as columns we
could convert them back into rows. It's time to do the UNPIVOT!
To do this replace pivot with unpivot. Again you need three things. First a column to store the
calorie totals for each food. Next, so we know which food these values came from,
another which will show the source column name. Finally list out the names
of the columns which will become rows. Here that's the foods.
So now we have a table with rows for each date and food. But the
source table didn't have the time I ate things. So we don't know if I've got
small snacks throughout the day or I'm eating all my cake in one go.
This is a general problem with unpivot. If the table you're unpivoting
contains aggregated values there's no general way to deaggregate them.
This can be a problem if you start with a table and then pivot it and then unpivot it again.
As we've seen pivoting is a powerful operation for converting rows
to column totals for days, weeks, or months. But it has many other
applications too, such as converting sporting results to league tables.
Univoting has fewer applications but it's still useful to have in your SQL toolkit.
Thanks for watching! If you enjoyed this and want to watch more
SQL videos click the links on screen now.