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

Question

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 |
+------+------+--------+

Answer 1

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;

Answer 2

Some SQL looks like this may work:

WITH UNION_TABLE AS (
  (
  SELECT SHOP, 'BUY' AS TYPE, BUY_PROFIT AS PROFIT
  FROM your_table_name
  )
  UNION ALL
  (
  SELECT SHOP, 'SELL' AS TYPE, SELL_PROFIT AS PROFIT
  FROM your_table_name
  )
)
SELECT SHOP, TYPE, PROFIT FROM UNION_TABLE limit 100