0

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?

Community
  • 1
  • 1
Evgenij Reznik
  • 16,978
  • 34
  • 97
  • 170

1 Answers1

1

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;
Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
  • Ok, that solves the problem described here, thank you. But my description was very simplified and I expected something with several IF-Statements. I will have to place another question with a better description. – Evgenij Reznik Aug 23 '16 at 08:52