Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    11

    Unanswered: Convert Multiple Column Hierarchy to rows with its level

    Hi All,

    I have hierarchy levels mentained in multiple columns in a table.
    I need to migrate this data into rows with its level defined.
    How to achieve the same.

    Example:
    I have location data as:
    SNo. LVL1 LVL2 LVL3 LVL4 LVL5
    1 A1 A2 A3 A4 A5
    2 B1 B2 B3 B4 B5
    3 C1 C2 C3 C4 C5


    After conversion my data should be like:
    Child_Locaion Parent Location Level
    A1 A1 1
    A2 A1 2
    A3 A2 3
    A4 A3 4
    A5 A4 5
    B1 B1 1
    B2 B1 2
    B3 B2 3
    B4 B3 4
    B5 B4 5
    c1 C1 1
    C2 C1 2
    C3 C2 3
    C4 C3 4
    C5 C4 5


    Thanks in advance.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    No response was yet.

    So, I wan to prvide (might be) a soluion.
    Though It was not tesed.

    Example 1:
    Code:
    SELECT CASE level
           WHEN 1 THEN lvl1
           WHEN 2 THEN lvl2
           WHEN 3 THEN lvl3
           WHEN 4 THEN lvl4
           WHEN 5 THEN lvl5
           END  AS Child_Locaion
         , CASE level
           WHEN 1 THEN lvl1
           WHEN 2 THEN lvl1
           WHEN 3 THEN lvl2
           WHEN 4 THEN lvl3
           WHEN 5 THEN lvl4
           END  AS Parent_Location
         , level
     FROM  location_data
         , (SELECT 1 AS level FROM dual UNION ALL
            SELECT 2          FROM dual UNION ALL
            SELECT 3          FROM dual UNION ALL
            SELECT 4          FROM dual UNION ALL
            SELECT 5          FROM dual
           )
     ORDER BY
           sno
         , level
    ;
    Last edited by tonkuma; 02-06-14 at 11:52.

Posting Permissions

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