Question 174052 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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 |
+——+——+——–+

Tags:

2

Answers


  1. 0

    Some SQL looks like this may work:

    WITH UNION_TABLE AS (
  (
  SELECT SHOP, 'BUY' AS TYPE, BUY_PROFIT AS PROFIT
  FROM your_table_name
  GROUP BY SHOP
  )
  UNION ALL
  (
  SELECT SHOP, 'SELL' AS TYPE, SELL_PROFIT AS PROFIT
  FROM your_table_name
  GROUP BY SHOP
  )
)
SELECT SHOP, TYPE, PROFIT FROM UNION_TABLE limit 100
    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search