Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    9

    Question Unanswered: SQL question: How to turn columns into rows?

    Hello everybody;


    I need to do something special in db2 v9.5:
    I have a table like this: (which is used by a messaging engine)

    create table resource
    (
    id integer,
    parent_id integer,
    name char(10)
    );

    There is a foreign key between fields id and parent_id.

    It contains data like this example:
    id parentid name
    -- --------- -------
    1 9 abc
    2 1 def
    3 2 ghi
    4 3 jkl
    9 7 mno
    19 17 pqr

    I need to extract all historical parents and put them in a row, so not in columns.
    If for example I would like to find all historical parents for id 4, then this should happen:
    Look for parentid of 4: this returns 3
    then look for parentid of 3: this returns 2
    then look for parentid of 2: this returns 1
    etc... until you no longer find a parent.

    The result of this should come into a row like this:
    ID parent1 parent2 parent3 parent4 parent5
    4 3 2 1 9 7

    With the following query I can get the historical parents:
    WITH temp (id, parentid, name,n) AS
    (SELECT a.* ,1 FROM resource a WHERE id = 4
    UNION ALL SELECT new.*, n + 1 FROM temp old ,resource new WHERE old.parentid = new.id AND old.n < 5 AND
    new.id = (SELECT xxx.parentid FROM resource xxx WHERE xxx.parentid = old.parentid) )
    SELECT ID, PARENTID FROM temp

    But this has 2 disadvantages:
    1. You need to give the max number of iterations that the query wil run (here n < 5).
    2. And it stores the parents in different records instead of all in 1 row:
    ID PARENTID
    ----------- -----------
    4 3
    3 2
    2 1
    1 9
    9 7

    Furthermore, the query should be able to run not only in db2 9.5, but also in PostGres...

    Any input is greatly appreciated.

    Thank you very much in advance;
    Carl

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1. You need to give the max number of iterations that the query wil run (here n < 5).
    It is not necessary to specify the max number of iterations.
    But, it may be better to specify limit of number of iterations to suppress SQL0347W message, like n < 1000(enough large number)

    2. And it stores the parents in different records instead of all in 1 row:
    Concatenate parents in recursive common-table-expression.

    Example 1: I don't know about PostGres.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    resource(id , parentid , name) AS (
    VALUES
      (  1 ,  9 , 'abc' )
    , (  2 ,  1 , 'def' )
    , (  3 ,  2 , 'ghi' )
    , (  4 ,  3 , 'jkl' )
    , (  9 ,  7 , 'mno' )
    , ( 19 , 17 , 'pqr' )
    )
    , temp (parentid , parent_list , n) AS (
    SELECT parentid
         , VARCHAR( RTRIM( CHAR(parentid) ) , 50 )
         , 1
     FROM  resource
     WHERE id = 4 
    UNION ALL
    SELECT new.parentid
         , parent_list || ', ' || RTRIM( CHAR(new.parentid) )
         , n + 1
     FROM  temp     old
         , resource new
     WHERE old.parentid = new.id
       AND old.n        < /*5*/ 1000
    /*
       AND new.id
           = (SELECT xxx.parentid
               FROM  resource xxx
               WHERE xxx.parentid = old.parentid
             )
    */
    ) 
    SELECT MAX(parent_list) AS parent_list
     FROM  temp
    ;
    ------------------------------------------------------------------------------
    
    PARENT_LIST                                       
    --------------------------------------------------
    3, 2, 1, 9, 7                                     
    
      1 record(s) selected.

  3. #3
    Join Date
    Mar 2011
    Posts
    9
    Thank you very much Mr. Tonkuma.
    I will try it out at work as soon as possible and I will keep you informed.
    Once more: Thank you.

    Yours sincerely;
    Carl

    Ps.: My father has known the founder of Sony Corporation personally.

  4. #4
    Join Date
    Nov 2011
    Posts
    334

    hi

    You can use the following sql to find all id's parents....

    WITH
    resource(id , parentid , name) AS (
    VALUES
    ( 1 , 9 , 'abc' )
    , ( 2 , 1 , 'def' )
    , ( 3 , 2 , 'ghi' )
    , ( 4 , 3 , 'jkl' )
    , ( 9 , 7 , 'mno' )
    , ( 19 , 17 , 'pqr' )
    )
    , temp (id,parentid , parent_list , n) AS (
    SELECT id,parentid
    , VARCHAR( RTRIM( CHAR(parentid) ) , 50 )
    , 1
    FROM resource
    --WHERE id = 4
    UNION ALL
    SELECT old.id,new.parentid
    , parent_list || ', ' || RTRIM( CHAR(new.parentid) )
    , n + 1
    FROM temp old
    , resource new
    WHERE old.parentid = new.id
    AND old.n < 1000
    )
    SELECT id, MAX(parent_list) as parent_list
    FROM temp group by id
    ;

  5. #5
    Join Date
    Mar 2011
    Posts
    9
    Hello people;

    Now I have another issue:
    They would like to have the result set reversed.

    Example:
    With the queries above we put all parents of a certain ID into 1 column.
    Eg. for ID '4' you get a record like this:
    ID 4
    ParentIDs: 3, 2, 1, 9, 7

    -> Actually the ParentIDs should be in reverse order now: 7, 9, 1, 2, 3

    Is there any easy way to do this?

    Thank you in advance.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why did you asked the last question?
    Couldn't you make the query from Example 1, by yourself?

    Modify Example 1 to concatenate parerentids in reverse order.
    More concrete, exchange "parent_list" and "RTRIM( CHAR(new.parentid) )" in Example 1,
    and modify last fullselect to choose a row having max(n).

    From "Example 1"
    Code:
    ...
    UNION ALL
    SELECT new.parentid
         , parent_list || ', ' || RTRIM( CHAR(new.parentid) )
         , n + 1
    ...
    ) 
    SELECT MAX(parent_list) AS parent_list
     FROM  temp
    ;
    Last edited by tonkuma; 01-23-12 at 11:09. Reason: Add "and modify last fullselect ..."

Posting Permissions

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