I inherited a rather large database which contains several hundred columns of data, and for most of these columns, there is a metadata column which specifies whether the data in that column was based off of the response to a survey or merely estimated by using one of several codes. All of these metadata columns' name's begin with the letter x. I need to create a view that contains all of the data from the original table, with the exception that all of the metadata columns have a function performed on the data in them.

For example, if I were only doing this with two columns, it would look something like this:

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 :

Code:

select "CREATE  ALGORITHM = UNDEFINED VIEW my_view AS SELECT"
                union
                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"
                union
                select "from YourTableName";

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.