SQL: The Ultimate Intermediate Guide to Learning SQL Programming Step by Step by Reed Mark

Author:Reed, Mark
Language: eng
Format: epub, pdf
Published: 2022-01-13T16:00:00+00:00

Razors

2016

37000.00

Razor Blades

2017

43000.00

Razor Blades

2017

45000.00

Razors

2017

46000.00

Razors

2017

48000.00

How to PIVOT Data in MySQL

MySQL does not support pivot, nor do a few other RDBMS databases such as Microsoft SQL Server and SQLite. However, like MySQL, some functions can simulate the functionality of pivot.

In MySQL, the case function can be used to pivot tables. The case function is a conditional statement. It will check to see if a condition is met before returning a result. It uses the same type of format as the “if … then … else … and is usually followed by an aggregate function or two such as when and then.

A simple explanatory example of using the case statement would be to consider a simple customer address log where you want to group customers by the towns they live in.

This simple script would look something like:

select residents, town, state, zip

from states

order by (CASE WHEN town is NULL THEN zip ELES state END);

The above script selects residents and sorts them by either their town, zip, or state.

In the next section, you are going to use the case function to pivot data using the prodsales table from the sales database you have created.

If you look at the data collected in the above prodsales table representation, you can see there are numerous ways you could manipulate and report on that data. One such report would be to view the number of sales per product per year as per the table below.

Pivoted Prodsales SQL Table

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.