I need to do this with a few hundred columns, though. Does anyone know how?
Copy and paste ?
You could pull all the column names from the information_schema and build the sql just using concat. Something a bit like this :
select "CREATE ALGORITHM = UNDEFINED VIEW my_view AS SELECT"
select concat( " ", c.column_name, ", my_native_function(x", c.column_name,
") as x", c.column_name, "," )
from information_schema.COLUMNS c
where c.table_schema = schema()
and c.table_name = "YourTableName"
select "from YourTableName";
I inherited a rather large database which contains several hundred columns of data
I assume you mean a table with several hundred columns of data - it generally causes all sorts of problems to hold this type of data this way (as columns rather than rows). If you held each column as a separate row of data (with an identifier for the question) then it would of been easy to generate the SQL. Another advantage is that it would be easier to add new questions. There would also be no limit to the questions you could ask (unsure if MySQL has a limit on the number of fields in a table). You could also add a structure to your questionnaire more easily if the data wasn't held in a flat format.