[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

Parsing a mysql row with 200 columns into rows

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.id    | fyi.item_name                      | eval_result.sa_# |  eval_result.ma, #
----------------------------------------------------------------------------------------
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.

Thanks
Chris
0
chris_thorn
Asked:
chris_thorn
  • 3
  • 3
  • 2
  • +2
2 Solutions
 
vbNewbie2009Commented:
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 fyi.id 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_# present...in case you want to calculate the instances where neither have occurred.
select fyi.id
	,fyi.item_name
	,isnull(eval_result.sa_#,-1)
	,isnull(eval_result2.ma_#,-1)
from fyi
	left join eval_result on substring(eval_result.sa_#,4,len(eval_result.sa_#)) = fyi.id
	left join eval_result2 on substring(eval_result2.ma_#,4,len(eval_result2.ma_#)) = fyi.id

Open in new window

0
 
chris_thornAuthor Commented:
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    |                |
0
 
mlmccCommented:
So you have 3 tables
FYI, SA, and MA

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

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

MA
  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.

mlmcc

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Anthony PerkinsCommented:
>>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:
http://www.experts-exchange.com/Database/MySQL/
0
 
James0628Commented:
 mlmcc,

 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 fyi.id to the corresponding sa_* and ma_* columns (eg. fyi.id 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 fyi.id 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 ...


 chris_thorn,

 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.

 James
0
 
chris_thornAuthor Commented:
James,
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.
Etc…

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.
0
 
mlmccCommented:
Thinking along normalized databases I think I would have

tblEvalHeader
EvalId
PersonId - Link to person table
ManagerId - Link to manager or person table
SA_Date
MA_Date


tblEvalDetails
EvalDetailId
fkEvalId - Link to tblEvalHeader
FactorId
ClusterId
EvalCriteriaId
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

mlmcc
0
 
James0628Commented:
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.

 James
0
 
chris_thornAuthor Commented:
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.
Chris
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now