Compiling an sql query, please?

Question

MySQL

There is a table in which there are two columns, one with values, the other with their statuses (for example: if the status is 1, then the value refers to manufactured products, if the status is 2, then the value refers to sold products). It is necessary that the query takes the value from the first column based on the value in the second column, and the output is two separate columns with manufactured and sold products. I would like to do it in one request.
Let's say there is a request:
SELECT value as value1, value as value2, status
it is necessary that, depending on the status field, the data from the value field gets either into value1 or into value2.

Answer 1

SELECT IF(status=1, value, NULL) AS value1, IF(status=2, value, NULL) AS value2 FROM table

Answer 2

something like this I
(SELECT value1 FROM table WHERE status = 1) UNION (SELECT value2 FROM table WHERE status = 2)
wrote in a hurry, I could be wrong ... if you throw off the data dump, I will help more precisely :)

Answer 3

As I understand it, you need this:

SELECT 
    IF(status = 1, value, NULL) AS value1,
    IF(status = 2, NULL, value) AS value2,
    status
FROM table
WHERE 1

Then you will have value1 in null for all products with status 2, and in value2 will be NULL for products with status 1

Answer 4

The given examples work, but if you enter a date limit, then everything collapses.
For example, like this: it works, and like this: it doesn’t work anymore, and if you try to insert a constraint into the if condition, it doesn’t work either. With the option: the same trouble ...
SELECT
DATE_FORMAT(dtcreate, '%d %M %Y') as dtcreate,
IF(state = 1, sum(plumb), NULL) AS value1,
IF(state = 3, sum(plumb), NULL) AS value2
FROM tblplumb
WHERE 1
GROUP BY DATE_FORMAT(dtcreate, '%d .%m.%Y')
SELECT
DATE_FORMAT(dtcreate, '%d %M %Y') as dtcreate,
IF(state = 1, sum(plumb), NULL) AS value1,
IF(state = 3, sum(plumb), NULL) AS value2
FROM tblplumb
WHERE dtcreate BETWEEN @start AND @STOP
GROUP BY DATE_FORMAT(dtcreate, '%d .%m.%Y')
SELECT value*(status=1) AS value1, value*(state=2) AS value2 FROM table

Answer 5

If for Oracle try so. If not oracle, then you can use DECODE instead of CASE
select
case
when value = 1 then status end value1,
case
when value = 2 then status end value2
from table