I need your help in one SQL Query for converting the example table given below. Where I need Facilities as columns.

Below table is how I'm expecting it to look like

In the expected result table even though 5th row data can be accommodated in 4th row itself like

my requirement requires it in a different row.

I found some pivot examples online, but they only show averaging or summing up the values and won't add the rows to already existing columns and display them all. I couldn't find a relevant post for this question. Please let me know if there is any relevant post.

Is it possible with Sql in the first place? If yes, then how?

CodePudding user response：

I think you're mistaken about the pivot part because there's no pivot happening here. This can be achieved with IF() or CASE expression functions added to a very basic MySQL syntax. So, this:

SELECT * FROM mytable;

Will return you all columns and rows. Then this:

SELECT Seasonid, Product, Facility, Price, ProductType
FROM mytable;

is basically the same as the query before-that will return all columns and rows. Only difference is here we're explicitly defining all the column names from the table. So, from here you only need to modify the Facility and Price part with conditional IF() or CASE() and define it twice in the SELECT section, like this:

SELECT seasonid,
       Product,
       CASE WHEN Facility='Montreal' THEN Price ELSE 0 END AS 'Montreal',
       CASE WHEN Facility='Mexico' THEN Price ELSE 0 END AS 'Mexico',
       ProductType
FROM mytable;

Or

SELECT seasonid,
       Product,
       IF(Facility='Montreal',Price,0) AS 'Montreal',
       IF(Facility='Mexico',Price,0) AS 'Mexico',
       ProductType
FROM mytable;

In which both queries are able to get your desired result.

Demo fiddle