Forum Stats

  • 3,757,263 Users
  • 2,251,216 Discussions


How to add category for respective variable

Costa Member Posts: 168 Blue Ribbon

Hi All,

From the below master table, I need to add category and sub-category for few variable like below

e.g Add category Fitness1 to exercise, diet and Running like below table. I tried all possible way but can not proceed whether I should split whole table into three child table and then create relation. can you please guide



  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,030 Red Diamond


    Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data. Always say which version of Oracle you're using (e.g.


  • mathguy
    mathguy Member Posts: 10,068 Gold Crown
    edited Nov 20, 2020 3:02PM

    Let me see if I understand the problem.

    You have the two tables you show in the post.

    You want to write a query that will have an output like the second table, but with three additional columns: Fitness1, Fitness2, Fitness3. Is this what you are asking?

    Now, the complicated part. I assume the first table shows us which "variable" falls in which category - and you want to use that information for the computations in the second table. That can be done - but you would need to be much more careful with the values in the VARIABLE column. Why are some of the names capitalized, but others aren't? This is the kind of carelessness that causes a lot of problems. (I see the same issue in your "subcategory" column.)

    The really complicated part: The problem has a plain SQL solution if all the "variables" and all the "categories" are known without seeing the data in the two tables. The first table shows which "variable" is in which "category" (and this classification can change, without requiring a modification to the query), but the variables themselves and the categories themselves don't change.

    If you may add "variables" in the future, or if you may add a new "category" (even without changing the variables), or if the names of variables or categories (or both) may change in the future, then you will likely need dynamic SQL for this. Best left for your reporting software - it will use dynamic SQL behind the scenes, you won't have to worry about it.

    Since the approach is very different depending on your answer to this fundamental question (are the "variables" and "categories" fixed, or you will only know them after you see the data), I won't propose a solution just yet. First answer the question, then we'll take it from there.

  • Mike Kutz
    Mike Kutz Member Posts: 5,628 Silver Crown

    can you please guide

    Tables with "MANY-TO-MANY" relationships require a "linking table".

    Your "master table" (1st image) would be the "linking table" in this case.

    As such, you wouldn't "create three child tables" but, instead, "create three child parent tables"

    schema design notes: I'd add a Surrogate Key to the "master table" while I was at it.

    My $0.02