Here is a SQL Fiddle
I have a table, inventory that has some data that looks like this:
+--------------+---------+---------------+--------------+------------+------------+
| pmkInventory | fnkPart | inventoryYear | inventoryNum | inventoryR | inventoryC |
+--------------+---------+---------------+--------------+------------+------------+
| 1 | 51 | 2016 | 6 | A | 4 |
| 2 | 2075 | 2016 | 40 | C | 4 |
| 3 | 50 | 2016 | 3 | A | 8 |
| 4 | 3 | 2016 | 600 | F | 3 |
| 5 | 3 | 2017 | 650 | F | 3 |
| 6 | 100 | 2016 | 2 | B | 1 |
| 7 | 50 | 2017 | 5 | A | 8 |
| 8 | 51 | 2017 | 16 | A | 4 |
+--------------+---------+---------------+--------------+------------+------------+
It has count and location information for each part, each year. There will be more than 2 years (not just 2016 and 2017 stored) but I only need to display two years at a time.
I need to come up with a SQL statement to return something like this:
+---------+---------------+--------------+------------+------------+---------------+--------------+------------+------------+
| fnkPart | inventoryYear | inventoryNum | inventoryR | inventoryC | inventoryYear | inventoryNum | inventoryR | inventoryC |
+---------+---------------+--------------+------------+------------+---------------+--------------+------------+------------+
| 51 | 2016 | 6 | A | 4 | 2017 | 16 | A | 4 |
| 2075 | 2016 | 40 | C | 4 | NULL | NULL | NULL | NULL |
| 50 | 2016 | 3 | A | 8 | 2017 | 5 | A | 8 |
| 3 | 2016 | 600 | F | 3 | 2017 | 650 | F | 3 |
| 100 | 2016 | 2 | B | 1 | NULL | NULL | NULL | NULL |
| 40 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 504 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+---------+---------------+--------------+------------+------------+---------------+--------------+------------+------------+
From my reading it sounds like I need some sort of Pivot table, and because MySQL doesn't have a built in one, I will need to use CASE statements. This question talks about pivot tables, but I'm confused how to use it without using the count function. I don't need to count anything as I have the data already stored in the database - I just need to display it.
A non pivot-table solution I came up with sort of works. It correctly shows the years, but doesn't show rows with null values. I have a part table with thousands of parts. I'm joining the inventory table to it. I thought that by doing a LEFT JOIN I would get all the parts, even if there wasn't data for the specified years in the inventory table.
Here is that:
SELECT p.pmkPart, p.partNumber,
i1.fnkPart, i1.inventoryYear, i1.inventoryNum, i1.inventoryR, i1.inventoryC,
i2.fnkPart, i2.inventoryYear, i2.inventoryNum, i2.inventoryR, i2.inventoryC
FROM part AS p
LEFT JOIN inventory as i1
ON p.pmkPart = i1.fnkPart
LEFT JOIN inventory as i2
ON p.pmkPart = i2.fnkPart
WHERE i1.inventoryYear = 2016
AND i2.inventoryYear = 2017
GROUP BY p.pmkPart
This query would give me something like this. Notice it's missing part 2075 and 100 because they do not values for the year 2017. It is also excluding the rest of the parts from the part table - that have no values in the inventory table.
+---------+------------+---------+---------------+--------------+------------+------------+---------+---------------+--------------+------------+------------+
| pmkPart | partNumber | fnkPart | inventoryYear | inventoryNum | inventoryR | inventoryC | fnkPart | inventoryYear | inventoryNum | inventoryR | inventoryC |
+---------+------------+---------+---------------+--------------+------------+------------+---------+---------------+--------------+------------+------------+
| 51 | AB-l34 | 51 | 2016 | 6 | A | 4 | 51 | 2017 | 16 | A | 4 |
| 50 | AB-l36 | 50 | 2016 | 3 | A | 8 | 50 | 2017 | 5 | A | 8 |
| 3 | C-5-BT | 3 | 2016 | 600 | F | 3 | 3 | 2017 | 650 | F | 3 |
+---------+------------+---------+---------------+--------------+------------+------------+---------+---------------+--------------+------------+------------+
Does anyone have any suggestions on how I can modify my existing query to get the correct result? Or how to create a pivot table that does what I want?
The years I'm selecting I need to be able to change, but I will only be selecting two at a time, 2016 and 2017, or 2016 and 2018, or 2017 and 2018, etc.
I've figured out how to do this using a combination of php and MySQL, but I am moving to a new platform where server side scripting is not an option, so need to move everything into SQL
