Parsing a mysql row with 200 columns into rows

Posted on 2011-05-02
Last Modified: 2012-05-11
I'm reporting from a mysql database.  The columns of the main data table look like :

id, user_id, sa_#, ma_#, comments

The # sign is a set of numbers like 5, 24, 61, 17, 30, 32, ...(60 in total) so sa_5 would correspond to ma_5 and so on.  The table saves evaluation results and the sa = self evaluation and ma = manager evaluation.  I need to match the eval_results table with the questions table stored in the fyi table which looks like

id, fact_id, clust_id, item_name, item_description.

The # portion of the column name matches value of the id column.  I need to pull the data in the following form.    | fyi.item_name                      | eval_result.sa_# |, #
5          "Technical Functionality"                    5                          5
24        "Competency"                                    4                          3

There are "N" number of questions since the tests are built dynamically based on a table called evals which turns on questions with a saved eval name.

Is there any way I can accomplish this with SQL because I'm trying to return the results in a record set.  If not, I am using crystal reports so is there a way of matching dynamic column names within a report.

Question by:chris_thorn
    LVL 1

    Expert Comment

    This option will pull an id reference from fyi, regardless of it being either present in the sa_# reference or the ma_# reference.  ...or both.

    in your example the second line is actually bringing a match on the of 24 to sa_# 4 and ma_# 3...this kind of makes me think there is a different column missing from your question.

    Let me know if there is another column and I can tweak it for you or if you have any questions.  This could also be flipped around to join the fyi table (fyi, and fyi2) against the eval_result table...but I assumed you would like to see all fyi values regardless of an sa_# present or ma_# case you want to calculate the instances where neither have occurred.
    from fyi
    	left join eval_result on substring(eval_result.sa_#,4,len(eval_result.sa_#)) =
    	left join eval_result2 on substring(eval_result2.ma_#,4,len(eval_result2.ma_#)) =

    Open in new window


    Author Comment

    Thanks again for the post but I'm not sure I understand what you've proposed as a solution.  The fyi table has 185 columns/fields.  Are you suggesting joining the eval_results that many times?  

    The following is just part of the schema but demonstrates when I meant by the "#" sign.  It was just a place holder for the numeric portion of the column name.

    | Field                | Type                         | Null | Key | Default | Extra          |
    | results_id           | int(10)                      |      | PRI | NULL    | auto_increment |
    | user_id              | int(10)                      | YES  |     | NULL    |                |
    | mngr_user_id         | int(10)                      | YES  |     | NULL    |                |
    | sa_date              | date                         | YES  |     | NULL    |                |
    | ma_date              | date                         | YES  |     | NULL    |                |
    | sa_5                 | int(5)                       | YES  |     | NULL    |                |
    | sa_24                | int(5)                       | YES  |     | NULL    |                |
    | sa_61                | int(5)                       | YES  |     | NULL    |                |
    | sa_17                | int(5)                       | YES  |     | NULL    |                |
    | sa_30                | int(5)                       | YES  |     | NULL    |                |
    | sa_32                | int(5)                       | YES  |     | NULL    |                |
    | sa_51                | int(5)                       | YES  |     | NULL    |                |
    | sa_2                 | int(5)                       | YES  |     | NULL    |                |
    | sa_14                | int(5)                       | YES  |     | NULL    |                |
    | sa_28                | int(5)                       | YES  |     | NULL    |                |
    | sa_46                | int(5)                       | YES  |     | NULL    |                |
    | sa_58                | int(5)                       | YES  |     | NULL    |                |
    | sa_16                | int(5)                       | YES  |     | NULL    |                |
    | sa_50                | int(5)                       | YES  |     | NULL    |                |
    | sa_39                | int(5)                       | YES  |     | NULL    |                |
    | sa_47                | int(5)                       | YES  |     | NULL    |                |
    | sa_62                | int(5)                       | YES  |     | NULL    |                |
    | sa_18                | int(5)                       | YES  |     | NULL    |                |
    | sa_19                | int(5)                       | YES  |     | NULL    |                |
    | sa_20                | int(5)                       | YES  |     | NULL    |                |
    | sa_27                | int(5)                       | YES  |     | NULL    |                |
    | sa_35                | int(5)                       | YES  |     | NULL    |                |
    | sa_52                | int(5)                       | YES  |     | NULL    |                |
    | sa_59                | int(5)                       | YES  |     | NULL    |                |
    | sa_63                | int(5)                       | YES  |     | NULL    |                |
    | sa_9                 | int(5)                       | YES  |     | NULL    |                |
    | sa_12                | int(5)                       | YES  |     | NULL    |                |
    | sa_13                | int(5)                       | YES  |     | NULL    |                |
    | sa_34                | int(5)                       | YES  |     | NULL    |                |
    | sa_57                | int(5)                       | YES  |     | NULL    |                |
    | sa_25                | int(5)                       | YES  |     | NULL    |                |
    | sa_56                | int(5)                       | YES  |     | NULL    |                |
    | sa_1                 | int(5)                       | YES  |     | NULL    |                |
    | sa_43                | int(5)                       | YES  |     | NULL    |                |
    | sa_53                | int(5)                       | YES  |     | NULL    |                |
    | sa_38                | int(5)                       | YES  |     | NULL    |                |
    | sa_48                | int(5)                       | YES  |     | NULL    |                |
    LVL 100

    Expert Comment

    So you have 3 tables
    FYI, SA, and MA

    FYI has
      ID - 1-200
      Description - Area of evlauation

      Identifying stuff - user and manager id, dates,
      SA# - value for that area

      Same as SA

    You need
      SA#, MA#, FYI.ID where FYI.ID = #

    I don't see any easy way to correlate the tables in either the database or in Crystal.
    Who devised this table schema?

    Normally tables are related based on a column value rather than the field names.


    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>Zones: MS SQL Server, SQL Query Syntax, Crystal Reports Software<<
    It seems to me this question would be better served in the MySQL zone, as it is unrelated to MS SQL Server.  See here:
    LVL 34

    Expert Comment


     I think there are just 2 tables, fyi and eval_result.  eval_result has a series of 60 sa_* and ma_* columns.  He wants to link to the corresponding sa_* and ma_* columns (eg. 5 linked to sa_5 and ma_5).  One way to handle that might be to somehow separate each pair of sa_* and ma_* columns into a separate row, although I can't think of a good way to do that.

     FWIW, I think the example results in the first post either have a typo in the second line, or are misleading.  I read the first line as showing 5 (Technical Functionality) matched to eval_result.sa_5 and eval_result.ma_5 (IOW, I thought that the 5's in the sa and ma columns were the column numbers), but then the second line shows 24, 4 and 3, which would be matching 24 to sa_4 and ma_3, so now I'm thinking that the numbers in the sa_# and ma_# columns are just sample values from those columns, in which case the two 5's in the first line are kind of misleading (at least, they were for me).  Anyway ...


     Is the table with all of the sa_* and ma_* columns fixed, or do the columns vary?  I ask because you mentioned that "the tests are built dynamically", and the partial schema that you posted shows the sa_* columns in no discernible order.  If the table had a set structure, I'd expect to see sa_1, sa_2, sa_3, etc.

     If the table structure is not fixed, that's another issue to deal with.  In particular, CR doesn't like datasources to change.


    Author Comment

    The eval_results table is fixed and the numbering of the sa_ and ma_ columns as you noted are not in numerical order.  They are instead in a logical order of the numerical value assigned to the competency which is part of a cluster and factor group.

    Factor 1.
          Cluster A.
                Competency (5)
                Competency (24)
          Cluster B.
                Competency (61)
                Competency (17)
    Factor 2.

    When I initially designed the table, I was thinking in PHP mode and thought I’d build the select query the same way I built the INSERT and UPDATE queries.  The eval() function in php works miracles but after much searching I can’t seem to find the same type of functionality in SQL.
    Since this is a new project, perhaps a quick redesign and code changes would be the best way to go as pointed out by mlmcc.

    Any additional thoughts would be appreciated.
    LVL 100

    Accepted Solution

    Thinking along normalized databases I think I would have

    PersonId - Link to person table
    ManagerId - Link to manager or person table

    fkEvalId - Link to tblEvalHeader
    SA value
    MA value

    Now you can link the tables in SQL to get all the rows
    SELECT tblEvalHeader.*, tblEvalDetails.*
    FROM tblEvalHeader INNER JOIN tblEvalDetails ON tblEvalHeader.EvalId = tblEvalDetails.fkEvalId

    The report can be grouped by FactorId then ClusterId
    Sort by EvalCriteriaId

    LVL 34

    Assisted Solution

    While I can see some logic to having a single row for each results_id, user_id, etc., with all of the sa and ma columns in that row, it also seems, off hand, like having all of those columns will really complicate some things.

     As for an Eval function in SQL, I don't work with PHP, but if I follow what Eval does, you're looking for "dynamic SQL".  That refers to creating a string that contains a SQL statement and then executing that string.  Look for the EXECUTE function for MySQL.  FWIW, dynamic SQL is not something that I use (I've never really needed it), but my impression is that it is very inefficient, compared to regular SQL statements (I presume basically because it's generated on the fly, so the db has no chance to optimize it), so I would consider it something of a last resort.  Of course I could be wrong about that.


    Author Closing Comment

    Both mlmcc and James presented invaluable help but in the end I split the table into three separate tables.  One to hold the common information for the evals (dates, user_id, etc...) one for multiple choice questions (ma_ and sa_ results) and finally a table to hold the past goals, comments and future goals.  All the tables were linked by the eval_id saved in the fist.

    As James pointed out, this seemed more efficient than using dynamic sql.

    Thanks helping me see the light.
    LVL 34

    Expert Comment

    You're welcome.  Glad I could help.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now