selecting a specific number from a column in a table into another table

Question

I have a table called processtime

id  | task1id | task1occ | task1time | task2id | task2occ | task2time | task3id | task3occ | task3time | task4id | task4occ | task4time | task5id | task5occ | task5time 
----+---------+----------+-----------+---------+----------+-----------+---------+----------+-----------+---------+----------+-----------+---------+----------+-----------
 10 |       9 |        0 |       300 |         |          |           |         |          |           |         |          |           |         |          |          
  7 |       5 |        1 |        20 |       6 |        1 |        45 |       1 |        0 |        60 |         |          |           |         |          |          
  9 |       6 |        1 |        45 |       7 |        1 |       120 |       2 |        0 |       110 |         |          |           |         |          |          
  8 |       5 |        1 |        20 |       6 |        1 |        45 |       3 |        1 |       200 |       1 |        0 |        60 |       4 |        1 |       300

and table called test1

 id | task1time | task2time | task3time | task4time | task5time 
----+-----------+-----------+-----------+-----------+-----------

what i'm trying to do is if a value in any of the occ columns in processtime = 0 then the task time is displayed in the test1 time columns.

So test1 table should look like this . :

id  | task1time | task2time | task3time | task4time | task5time 
----+-----------+-----------+-----------+-----------+----------- 
 10 |    300    |           |           |           |     
 7  |           |           |     60    |           |
 9  |           |           |     60    |           |
 8  |           |           |           |     60    |

I have tried using OR and AND functions but they do not work, i was thinking a IF function might work , but don't know where to start if one

Many Thanks

Dave

Wait...does `test1` even exist right now? You seem to be asking for 2 different things. Do you want to _display_ your suggested output, or do you want to _populate_ `test1` with that data? — Tim Biegeleisen, Jun 28 '18 at 10:54

score 1 · Accepted Answer · answered Jun 28 '18 at 10:56

Here is a basic query which should work as you intend:

SELECT
    id,
    CASE WHEN task1occ = 0 THEN task1time END AS task1time,
    CASE WHEN task2occ = 0 THEN task2time END AS task2time,
    CASE WHEN task3occ = 0 THEN task3time END AS task3time,
    CASE WHEN task4occ = 0 THEN task4time END AS task4time,
    CASE WHEN task5occ = 0 THEN task5time END AS task5time
FROM yourTable;

If you want to populate the test1 table with this information, then you may use an INSERT INTO ... SELECT, where the SELECT is just the above query.

selecting a specific number from a column in a table into another table

1 Answers1