Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    1

    Unanswered: transpose results grouped by parentid...

    I have an interesting problem...

    A line in a chart of accounts is made up of a number of levels.
    I'm storing them in a table like this
    coalinekey| level | levelname
    1 | 1 | FIRST LEVEL
    1 | 2 | second level
    1 | 3 | third level
    2 | 1 | level 1 line 2
    2 | 2 | level 2 line 2
    2 | 3 | level 3 line 2


    I want to return a result set that looks like this...

    line| level1 | level2 | level 3|
    1 | FIRST LEVEL | second level | third level
    2 | level1 line2 | level 2 line 2 | level 3 line 3|


    I can to it with subqueries if i know the line but
    if i want to return multiple lines the subquery would return multiple results and is therefore invalid..

    I've considered a self-join but i can't figure out how to make that work.

    I know i can do this with a cursor but it is very slow...

    How does one handle this case efficiently?

    thanks!

    brad
    Last edited by bwlang; 12-25-02 at 21:54.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this self join --

    select l1.coalinekey as line
    , l1.levelname as level1
    , l2.levelname as level2
    , l3.levelname as level3
    from chartofaccounts l1
    left outer
    join chartofaccounts l2
    on l1.coalinekey = l2.coalinekey
    left outer
    join chartofaccounts l3
    on l1.coalinekey = l3.coalinekey
    where l1.level = 1
    and l2.level = 2
    and l3.level = 3

    rudy
    http://rudy.ca/

Posting Permissions

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