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
  • 17,916
  • 39
  • 104
  • 181

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,242,037
  • 58
  • 646
  • 786
  • 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