user1170330 user1170330 - 3 months ago 8
SQL Question

Iterate through every row and create new table

Suppose I have the following table

T1
:

| col1 | col2 | col3 |
|------|------|------|
| 0 | 1 | 1 | // 1
| 1 | 0 | 1 | // 1
| 0 | 1 | 0 | // 0


I now need to iterate every row, create a new table
T2
and populate it with a
1
, whenever there are two
1
in a row in
T1
.

So the new table
T2
would look like:

| res |
|-----|
| 1 |
| 1 |
| 0 |


Should I really iterate through each row, as described here, or is there a more efficient way?

Answer

You can add up the "1"s. Assuming each column is either 0 or 1 (as in the question):

select (case when col1 + col2 + col3 = 2 then 1 else 0 end) as res
from t1;

Add into t2 after the select if you really want another table.

Note: SQL tables and results sets represent unordered sets (unless there is an order by for the creation of a result set). So, if you create a new table, the rows are unordered and will not correspond to the original data. You might simply want a query like this:

select t1.*,
       (case when col1 + col2 + col3 = 2 then 1 else 0 end) as res
from t1;