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...
select l1.coalinekey as line
, l1.levelname as level1
, l2.levelname as level2
, l3.levelname as level3
from chartofaccounts l1
join chartofaccounts l2
on l1.coalinekey = l2.coalinekey
join chartofaccounts l3
on l1.coalinekey = l3.coalinekey
where l1.level = 1
and l2.level = 2
and l3.level = 3