locked
How to get column names of Oracle table-columns having values equal to 1 in the same row? RRS feed

  • Question

  • User1068864436 posted

    Hi Everyone,

                      First of all I really hope that i am at the right place for my query as this is my first post. I am using Oracle 10g along with Visual Studio 2010.

    I have an Oracle table named Segment_Restriction with 'all the columns as integer' as shown below:

    subtype_id segment_ID segment_h1 segment_h2
    102 0 14 15
    102 14 0 1
    102 15 1 0

    Now if you can see the row where Segment_ID is 0, i.e, first row, this is taken as a base row.

    Now if suppose i say that i want the row with segment_ID = 14

    So i get the following:

    subtype_id segment_ID segment_h1 segment_h2
    102 14 0 1

    Here comes my question...

    Now, further in this row, i need to find the column names of columns having values = 1 and also the first row value of that column (column value with 0 segment id).

    So in this example, i should get segment_h2 as the column name and 15 as the first row contains 15 for segment_h2.

    How do I do this. I hope my question is clear...

    I need help as early as possible. I am finding this complicated and need this on urgent basis as my clients need this logic done by evening.

    Please reply at the earliest.

    Wednesday, September 25, 2013 3:46 AM

Answers

  • User269602965 posted

    Step 1: It will be easier to use Oracle Analytics if you first normalize your table into this layout (either as new table or view):

    SUBTYPE_ID

    SEGMENT_ID

    SEGMENT

    SEGMENT_VALUE

    102

    0

    SEGMENT_H1

    14

    102

    0

    SEGMENT_H2

    15

    102

    14

    SEGMENT_H1

    0

    102

    14

    SEGMENT_H2

    1

    102

    15

    SEGMENT_H1

    1

    102

    15

    SEGMENT_H2

    0

     

     

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 25, 2013 7:57 PM

All replies

  • User1068864436 posted

    Hi guys,

              Please reply soon. Help needed urgently on this.

    Wednesday, September 25, 2013 5:32 AM
  • User1068864436 posted

    Guys! I need some advice atleast on this... PLease Please Please reply someone...

    Wednesday, September 25, 2013 2:31 PM
  • User269602965 posted

    Step 1: It will be easier to use Oracle Analytics if you first normalize your table into this layout (either as new table or view):

    SUBTYPE_ID

    SEGMENT_ID

    SEGMENT

    SEGMENT_VALUE

    102

    0

    SEGMENT_H1

    14

    102

    0

    SEGMENT_H2

    15

    102

    14

    SEGMENT_H1

    0

    102

    14

    SEGMENT_H2

    1

    102

    15

    SEGMENT_H1

    1

    102

    15

    SEGMENT_H2

    0

     

     

     

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 25, 2013 7:57 PM
  • User1068864436 posted

    Thanks for the suggestion... Would put it accross my team. Lets see what they. I really appreciate your reply. Thanks again Lannie... :)

    Thursday, September 26, 2013 8:23 AM
  • User1068864436 posted

    Did not exactly use your suggestion. But got a hint to do it. Temporarily working it out by looping the datatables... Would get this working as a permanant solution. Thanks Lannie for your help... Laughing

    Monday, September 30, 2013 8:50 AM