[SOLVED] Transform 2 'number' columns to 'varchar' and 'number' columns depending on column names

Transform 2 'number' columns to 'varchar' and 'number' columns depending on column names

I have a table with the following structure:

+------+--------------+--------------+
| SHOP | BUY_PROFIT   | SELL_PROFIT  |
+------+--------------+--------------+
| A    |           10 |           15 |
+------+--------------+--------------+

I need to write select statement to make it look like this:

+------+------+--------+
| SHOP | TYPE | PROFIT |
+------+------+--------+
| A    | BUY  |     10 |
| A    | SELL |     15 |
+------+------+--------+

Solution

You can create the desired output by using a union of two selections:

SELECT SHOP, "BUY" AS TYPE, BUY_PROFIT AS PROFIT FROM shop_table 
UNION
SELECT SHOP, "SELL" AS TYPE, SELL_PROFIT AS PROFIT FROM shop_table;

You can take this a step further and order this by shop and test for null values if desired:

SELECT SHOP, "BUY" AS TYPE, BUY_PROFIT AS PROFIT FROM shop_table WHERE BUY_PROFIT IS NOT NULL
UNION
SELECT SHOP, "SELL" AS TYPE, SELL_PROFIT AS PROFIT FROM shop_table WHERE SELL_PROFIT IS NOT NULL
ORDER BY SHOP;