Adding one row as a column to existing columns

user_6822185 0
Viewed 26

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

Seasonid Product Facility Price Product Type
1 Socks Montreal 24 Wool
2 Slippers Mexico 50 Poly
3 Slippers Montreal 27 Rubber
4 Socks Mexico 24 Cotton
5 Socks Montreal 26 Cotton

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

seasonid Product Montreal Mexico Product Type
1 Socks 24 0 Wool
2 Slippers 0 50 Poly
3 Slippers 27 0 Rubber
4 Socks 0 24 Cotton
5 Socks 26 0 Cotton

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

seasonid Product Montreal Mexico Product Type
4 Socks 26 24 Cotton

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?

mysql sql
1 Answers

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

user_109106920
Related