CODEWITHSUNDEEP
sqlsqlite
Konrad
Konrad

Reputation: 21

SQLite. How to return 1 and 0 in different columns if specific values occurs

Hi I am new to SQL and especially SQLite which is expurgated in comparison to sql as I've just learned.

I've got a table which looks like this:

id_foo | foo_val
________________
  1    |    3
  1    |    1
  2    |    2

I need a query that would give me a table with values 1 and 0 if specific value occured:

id_foo | val1 | val2 | val3 | val4 |
_______|______|______|______|______|
  1    |   1  |   0  |  1   |  0   |
  2    |   0  |   1  |  0   |  0   |

I've tried to do this with "if exists" command, but "sqliteman" doesn't recognise this command :/

Upvotes: 1

Views: 292

Answers (2)

mu is too short
mu is too short

Reputation: 434685

SQLite uses 1 and 0 for booleans so you can do this if you don't like Erwin's CASE:

select id_foo,
       max(foo_val = 1) as val1,
       max(foo_val = 2) as val2,
       max(foo_val = 3) as val3,
       max(foo_val = 4) as val4
from pancakes
group by id_foo
order by id_foo

But keep in mind that use CASE should work everywhere.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656942

That should do the trick:

SELECT id_foo
      ,max(CASE WHEN foo_val = 1 THEN 1 ELSE 0 END) AS val1
      ,max(CASE WHEN foo_val = 2 THEN 1 ELSE 0 END) AS val2
      ,max(CASE WHEN foo_val = 3 THEN 1 ELSE 0 END) AS val3
      ,max(CASE WHEN foo_val = 4 THEN 1 ELSE 0 END) AS val4
FROM   tbl
GROUP  BY id_foo
ORDER  BY id_foo

Upvotes: 1

Related Questions