Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2005
    Posts
    7

    Question Unanswered: Table transformation query

    I need to transform a table of the form:
    holding name addr
    in which holding -> name is 1 to many (up to 9) and name -> addr is 1 to 1
    to the form:
    holding namecount name1 addr1 name2 addr2 name3 addr3 ...
    In a recent posting I was shown how to achieve this for names only, i.e.
    holding namecount name1 name2 name3 ...
    can be derived from the query:
    select holding,
    count(distinct t1.name) as namecount,
    min(t1.name) as name1,
    min(t2.name) as name2,
    min(t3.name) as name3,
    ...
    from mytable t1
    left join mytable t2 on t1.holding = t2.holding and t2.name > t1.name
    left join mytable t3 on t2.holding = t3.holding and t3.name > t2.name
    ...
    group by t1.holding;
    Does anyone know how to generalise this query to the scenario I am trying to solve?

  2. #2
    Join Date
    Aug 2005
    Posts
    7
    I figured this one out for myself eventually.
    The way to do it is through the use of a common table expression and more outer joins.
    Code:
    with holding_expr (holding_a, namecount, name1, name2, name3,  ...) as (
      select t1.holding as holding_a
        ,count(distinct t1.name) as namecount
        ,min(t1.name) as name1
        ,min(t2.name) as name2
        ,min(t3.name) as name3
        ...
      from mytable t1 
        left join mytable t2 on t1.holding = t2.holding and t2.name > t1.name
        left join mytable t3 on t2.holding = t3.holding and t3.name > t2.name
        ...
      group by t1.holding
    )
    select holding_a, namecount, name1, t1.addr as addr1,
           name2, t2.addr as addr2,
           name3, t3.addr as addr3,
           ...
    from holding_expr
         left join mytable t1 on holding_a = t1.holding and name1 = t1.name
         left join mytable t2 on holding_a = t2.holding and name2 = t2.name
         left join mytable t3 on holding_a = t3.holding and name3 = t3.name
         ...
     ;

Posting Permissions

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