423,103 Members | 1,347 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,103 IT Pros & Developers. It's quick & easy.

Join field name to row data

Seth Schrock
Expert 2.5K+
P: 2,886
I really don't think that what I'm asking is possible, but I'm hoping that there is some trick that I don't know about.

I have an asset tracking system (that I didn't create, but have no access to change design) that creates a new table for each asset type entered with the custom fields. However, the field names are like Field_10014, Field_10015, Field_10016, etc. and the field names are stored in a separate table with the ID of 10014, 10015, 10016, etc. So right now, I can easily pull my data by joining the Asset table to the the Asset_Data_12 table, but then my field names are Field_10014. My hope is that somehow I can translate the field names into the actual descriptions (Field_10014 = Model, Field_10015 = Serial Number, etc.).

To help describe the current design, below is the tables with their field names and some sample data.

Expand|Select|Wrap|Line Numbers
  1. table - ASSET
  2. ID   |ASSET_TYPE_ID |NAME        |ASSET_DATA_ID |LOCATION_ID | STATUS_ID
  3. 650   12             Cov Mem 5    34             175          5
  4. 651   12             Cov Mem 6    35             175          5
  5. 652   12             Cov Mem 7    36             175          5
The Asset_type_id then correlates to the table name where the custom fields' data is stored.
Expand|Select|Wrap|Line Numbers
  1. table - ASSET_DATA_12
  2. ID   |FIELD_10017   |FIELD_10014       |FIELD_10015    |FIELD_10047
  3. 34                   WS-C2960X-48LPS-L  FOC123456789    172.19.0.199
  4. 35                   WS-C2960X-48LPS-L  FOC123AB6789    172.19.0.199
  5. 36                   WS-C2960X-48LPS-L  FOC123AB67ZY    172.19.0.199
Now the field names for the custom fields are stored in Asset_Field_Definition
Expand|Select|Wrap|Line Numbers
  1. table - ASSET_FIELD_DEFINITION
  2. ID      |ASSET_TYPE_ID |FIELD_NAME       |FIELD_TYPE   |ORDINAL
  3. 10014    12             Model             text          0
  4. 10015    12             Serial Number     text          1
  5. 10017    12             Comments          notes         3
  6. 10047    12             IP Address        text          2
So if I want to pull a report that gives the asset name, location, status, and model, then I can easily join the asset and asset_data_12 tables together, but then I will end up with FIELD_10014 as my field name instead of Model. I know that this is a terrible design (and I have half a mind to tell the company who designed it how they could do it so much better), but this is what I'm stuck with. I could write a program that would do what I need for me, but I'm wanting a purely SQL solution.

Possible or impossible?
Jun 7 '18 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,235
Normally I would use a SQL pivot to do something like this but I don't think MySQL has pivot.

That being said, it's probably possible to write a stored procedure to generate dynamic SQL to rename all the fields. While this would be a solution wholly within MySQL, it wouldn't be standard SQL so I'm guessing that's outside the scope of what you're looking for.
Jun 7 '18 #2

Seth Schrock
Expert 2.5K+
P: 2,886
I can't add stored procedures to the database, so yes it is outside the scope of what I'm looking for. Obviously I can use aliases, but I was hoping for something that could be easily copied between asset types. Oh well, it was worth a shot.
Jun 7 '18 #3

Post your reply

Sign in to post your reply or Sign up for a free account.