Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Unanswered: modifying several hundred columns which match a certain pattern

    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:

    Code:
    CREATE  ALGORITHM = UNDEFINED VIEW `my_view` AS 
    SELECT data1, 
    my_native_function(xdata1) AS 'xdata1', 
    data2, 
    my_native_function(xdata2) AS 'xdata2'
    I need to do this with a few hundred columns, though. Does anyone know how?

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •