Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210

    Unanswered: Can I turn a table on it's side?

    I'm new to MySQL but our ERP is migrating to it in 3 months. Bye Bye SQL Server

    The vendor does this interesting thing where the product options don't have pre-assigned columns in the LineItem table. Instead they have tables called "Prompts" and "Prompt Answers".

    OrderLineItem table has a one-to-many relationship to OrderLineItemPromptAnswers.

    Example "Prompts" on the order are:
    • Width
    • Height
    • Color
    • and about 20 to 30 more, depending on what product is ordered


    I need to return a single LineItem row with prompt-answers converted into columns.

    We have dozens of different products, each with at least 20 prompts. Most products have prompts unique to that product.

    Normal grouping doesn't put the PromptAnswer values into separate columns.

    Oh please. Is there an easy solution?

    PS: I can (if I must) port this database into SQL Server 2005.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you should be able to do this using a stored procedure or whatever they are called in MySQL
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What you are trying to achieve is called a pivot table. However, the fact that you want to return variable number of columns per row may be a problem. Some may have one response others may have 5 responses.

    I think healdem is right and stored procedure is one way of tackling it or generating the response through your application code.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Since it appears that you can still use SQL Server 2005, you could easily build the desired structure using SELECT FOR XML against a linked server to your MySQL database.

    What you are trying to find is a variation on a Pivot Table. A "vanilla" Pivot Table just swaps rows for columns. Usually when I see the kind of request that you've got it is more common to have people ask for a PK of some sort with a string containing all of the values of the columns. The ragged columns are unusual because most code can't handle them.

    If you didn't have SQL Server, then the answer depends on your MySQL vintage. I just installed an application that using MySQL 3 for a client (ewww), and on MySQL prior to version 5 I'd suggest using PHP (portable, fast, and best practice). If you have a current Oracle supplied version of MySQL provided with your ERP solution you probably have an Oracle specifc feature that does exactly what you've described (I don't have the docs handy to get the feature name, but I could find it). If you have a current version of MySQL that wasn't provided with your ERP package, a MySQL stored procedure seems like a good bet.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Cheers Pat - this was a huge help!

    I'll look for that Oracle feature you mentioned. The MySQL is the latest free download from Oracle. Hopefully that's not something only in the $10,000 Enterprise version.

    Sorry; we're cramming in an entire ERP Software switch (manufacturing, BOM, Order Entry, etc) into 3 MONTHS! I have the honor of being quasi project manager (Lord save me). Upper management won't hear of stretching it out.

    Sooo; my apologies at being remiss in not thanking you earlier.

  6. #6
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by it-iss.com View Post
    What you are trying to achieve is called a pivot table. However, the fact that you want to return variable number of columns per row may be a problem. Some may have one response others may have 5 responses.

    I think healdem is right and stored procedure is one way of tackling it or generating the response through your application code.
    Thanks Healam and it-iss.com. Excellent point about the varying column numbers being a big problem. Although; I can probably do some fancy coalesce statements to fill in the blanks for missing ones.

    I'm thinking, if I can't get the pivot-table stuff to work, I'll build a flat Order-Line-Item record that has all possible fields - with many spares for expansion. Then I'll run a scheduled job to populate it that utilizes a function-call. For orders not yet populated, I can call the function.

    Mostly; anything that has to drill into Order-Option-Values are data mining so current (today's) orders don't matter. For simple things like Sales reports, I'll ignore these over-normalized tables.

    I could use the Stored Procedure approach on all queries to it for making sure it has the latest stuff - if I can get around the obvious deadlock issues.

    MySQL. The more I see, the more I like TSQL.

  7. #7
    Join Date
    Nov 2012
    Posts
    2
    I thnk that are trying to know something about pivot table. However, the fact that you want to return variable number of columns per row may be a problem. Some may have one response others may have 5 responses.

    please review following link to know more about this topic and you learn more about Mysql quries:

    I can not execute mysql query | Techyv.com

  8. #8
    Join Date
    Nov 2012
    Posts
    2

    Mysql Quries

    You can do this by creating Mysql Queries, if you know how to run then it is very easy for you to formate your table as per your requirements, i would like to suggest following website to know more about this topic.

    I can not execute mysql query | Techyv.com

Posting Permissions

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