change the query to return categorized output-CodePudding

I have a query like this:

select * from my_table;

and the result is like below:

name       x1    x2    x3
----       ---- ---   ---
data       -2    3      -1

I want to change the query to divide positive and negative output into separated row and the result change to :

name       x1    x2    x3
----       ---- ---   ---
data       -2    3      -1
data       -2    null   -1
data       null  3      null

CodePudding user response：

If your query is complicated, you can put it into a sub-query factoring (WITH) clause and then use UNION ALL and CASE expressions:

WITH your_query (name, x1, x2, x3) AS (
  select * from my_table
)
SELECT *
FROM   your_query
UNION ALL
SELECT name,
       CASE WHEN x1 < 0 THEN x1 END,
       CASE WHEN x2 < 0 THEN x2 END,
       CASE WHEN x3 < 0 THEN x3 END
FROM   your_query
UNION ALL
SELECT name,
       CASE WHEN x1 >= 0 THEN x1 END,
       CASE WHEN x2 >= 0 THEN x2 END,
       CASE WHEN x3 >= 0 THEN x3 END
FROM   your_query;

CodePudding user response：

   SELECT *
    FROM   my_Table
    SELECT name,
           CASE WHEN x1 < 0 THEN x1 else Null END,
           CASE WHEN x2 < 0 THEN x2 else Null END,
           CASE WHEN x3 < 0 THEN x3 else Null END
    FROM   my_Table
    UNION ALL
    SELECT name,
           CASE WHEN x1 >= 0 THEN x1 else Null END,
           CASE WHEN x2 >= 0 THEN x2 else Null END,
           CASE WHEN x3 >= 0 THEN x3 else Null END
    FROM   my_Table;