Mysql pivot query - navada chart style

This is not a final solution for your question but will hopefully lead you to it. It is somewhat easier to see the issues now that you have posted some code - I also notice that you have added customer_name to the sample data and are including that in the join. A better solution would be to have a transaction id for every shipment (and return). Including the customer_name in both tables does not conform to normalisation and to be honest having the returns in a separate table is not a good design (in my opinion).

Based on your query you also didn't supply us with the correct sample data '2021-09' is a string, not a date but you attempt to use the month() function on it. Also on that subject the MONTH() function returns an integer but you are comparing it to a string - '10'. It will probably work, but it is not good practice.

You are using 'NULL' instead of NULL - is there a specific reason for that? It's not really good practice as it means you have to test for that specific string instead of exploiting built-in functions that can handle null values.

You have unnecessary sub-queries

FROM
(
	SELECT * From ProductShip
)a
LEFT JOIN
(
	SELECT * From ProductReturn
)b ON  a.SN = b.SN AND a.Date_Shipped < b.Date_Returned AND  a.Customer_Name = b.Customer_Name

is the same as

FROM ProductShip a
LEFT JOIN ProductReturn
	b ON  a.SN = b.SN AND a.Date_Shipped < b.Date_Returned AND  a.Customer_Name = b.Customer_Name

except the latter is easier to read and executes faster.

Your IF statement is unnecessary - instead of

Case
WHEN a.Date_Shipped IS NOT NULL Then (
	IF (month(a.Date_Shipped) < '10',
	CONCAT(year(a.Date_Shipped),'-0',month(a.Date_Shipped)),
	CONCAT(year(a.Date_Shipped),'-',month(a.Date_Shipped))
	) 
) Else 'NULL' 
end as Ship_Month,

You could just use the DATE_FORMAT() function and allow it to return NULL naturally if Date_Shipped is null (if it ever could be ... how do you return something that was never shipped?). E.g.

DATE_FORMAT(a.Date_Shipped, 'yyyy-MM') as Ship_Month

So your query can be very much simplified to something like the following - CAVEAT I can't access MySQL at the moment so this is completely untested in MySQL - I have changed the MS-SQL functions to their MySQL equivalents from my testing, but I may have made mistakes. Note I am also returning the string 'null' instead of NULL as you may have a good reason for that

Select a.SN,
ifnull(DATE_FORMAT(a.Date_Shipped, 'yyyy-MM'), 'null') as Ship_Month,
a.customer_name, 
ifnull(DATE_FORMAT(b.Date_Returned, 'yyyy-MM'), 'null') as Return_Month
FROM ProductShip a
LEFT JOIN ProductReturn b 
       ON  a.SN = b.SN AND a.Date_Shipped < b.Date_Returned AND  a.Customer_Name = b.Customer_Name;

You can redirect the output from your query into a temporary table (MySQL :: MySQL 8.0 Reference Manual :: 13.1.20.2 CREATE TEMPORARY TABLE Statement[^]) or use a Common Table Expression (MySQL :: MySQL 8.0 Reference Manual :: 13.2.15 WITH (Common Table Expressions)[^]) or even use it as sub-query. I would personally use a temporary table as you may want to visit the results more than once to get your final results

You can then PIVOT on that data to get the results you want - see Pivot Tables in MySQL[^]