Looping within SQL(ite) SELECT

Question

The following SQLite query works:

SELECT Name,
(CASE 
WHEN P1=1 THEN 1 
WHEN P2=1 THEN 2 
WHEN P3=1 THEN 3 
WHEN P4=1 THEN 4 
WHEN P5=1 THEN 5 
ELSE NULL
END) AS Col
FROM table

but is there a way to loop then WHEN statements? Following this question and answer I tried:

SELECT Nachname, Vorname,
(CASE 
DECLARE @i int = 0
  WHILE @i < 5 BEGIN
    SET @i = @i + 1
    WHEN P@i=1 THEN @i 
  END
END) AS Col
FROM table

but this didn't work, saying: error in statement: near "@i": syntax error.

For full information I am applying this statement through a sqldf function on R.

Answer 1

SQLite has no mechanism to create column names dynamically.

You have to list all columns by hand, or create the SQL query dynamically from your program.