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

    Unanswered: Recursive : Columns into Rows in IBM DB2 v9.7

    Hi All,

    I have a problem with recursive sql. Please let me know the best way to achieve the result. I need to convert columns into rows.

    Input:
    Code:
    LEVEL PREDECESSOR SUCCESSOR
    0	X             A		
    0	Y             B
    1	A             O
    1	B             P
    2       O             E
    2       P             F
    
    Output
    LEVEL PREDECESSOR SUCCESSOR   LEVEL    SUCCESSOR   LEVEL  SUCCESSOR
    0	X             A		1         O          2        E
    0	Y             B		1         P          2        F

  2. #2
    Join Date
    Apr 2011
    Posts
    2

    Converting Columns into Dynamic Rows

    Hi Everyone,

    I have the following input and I need to covert the columns into rows according to the level. Please let me know the best approach to accomplish.
    Input:
    Code:
    LEVEL PREDECESSOR SUCCESSOR
    0	X             A		
    0	Y             B
    1	A             O
    1	B             P
    2       O             E
    2       P             F
    
    Output:
    LEVEL PREDECESSOR SUCCESSOR   LEVEL    SUCCESSOR   LEVEL  SUCCESSOR
    0	X             A		1         O          2        E
    0	Y             B		1         P          2        F
    Thanks in advance

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use as many JOINS as you need to create the strcuture. its going to be somethign like:-
    Code:
    SELECT L1.LEVEL,
      L1.PRE,
      L1.SUCC,
      L2.LEVEL,
      L2.SUCC,
      L3.LEVEL,
      L3.SUCC 
    FROM MYTABLE AS l1
    LEFT JOIN MYTABLE AS L2 ON (L1.SUCC = L2.PRE AND L1.LEVEL=0)
    LEFT JOIN MYTABLE AS L3 ON (L2.SUCC = L3.PRE AND L2.LEVEL=1)
    WHERE L1.LEVEL=0
    the level column may be superfluous as you can always identify the 'top' level by finding items which don't have a successor (possibly as a sub query)
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Check pivot/ unpivot. there are many examples on this site. Tonkuma has given quite a few of them.
    Dave

Posting Permissions

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