Re: Using a CTE for hierarchy data, from bottom up
- From: "Tom Cooper" <tomcooper@xxxxxxxxxxx>
- Date: Fri, 14 Aug 2009 11:44:32 -0400
The Where ID In (10,20) is just an example because your example said you wanted ID's 10 and 20. But in the general case, you must have some method of choosing which ID's you want. So you just replace
Where ID In (10,20)
with
Where <whatever condition selects the ID's you want to show as BASE_ID's in the final result>
If there is some reason you don't want the WHERE clause in the CTE, you could move it to the outer query like
From EmployeeLevels elInner Join AdjustAmounts a On el.BASE_ID = a.BASE_ID
Where <whatever condition selects the a.BASE_ID's you want in the final result>
Order By a.BASE_ID, Level Desc;
I, however, would not recommend that you do that since unless the query optimizer is a lot smarter than I think it is, moving the where to the outer query will cause the CTE to roll up every employee, and then the outer query will select only the employees you want. If you put the where in the CTE, it will rollup only the employees you want.
Tom
"Michael Cortese" <mjc606@xxxxxxxxx> wrote in message news:7366b1e0-1d1d-47d5-aeee-f471f72fb70b@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Aug 14, 12:59 am, "Tom Cooper" <tomcoo...@xxxxxxxxxxx> wrote:
Oops, instead try
Create Table #employee (ID int, MGR_ID int);
Insert #employee(ID, MGR_ID)
Select 5, NULL
Union All Select 6, 5
Union All Select 10, 6
Union All Select 15, NULL
Union All Select 16, 15
Union All Select 20, 16
Union All Select 25, NULL
Union All Select 26, 25
Union All Select 30, 26;
With EmployeeLevels As
(Select ID As BASE_ID,
-1 As InverseLevel,
MGR_ID
From #employee
Where ID In (10, 20)
Union All
Select el.BASE_ID,
el.InverseLevel - 1,
e.MGR_ID
From #employee e
Inner Join EmployeeLevels el On el.MGR_ID = e.ID),
AdjustAmounts As
(Select BASE_ID,
MIN(InverseLevel) As AdjustAmount
From EmployeeLevels
Group By BASE_ID)
Select el.BASE_ID,
InverseLevel - AdjustAmount + 1 As Level,
el.MGR_ID
From EmployeeLevels el
Inner Join AdjustAmounts a On el.BASE_ID = a.BASE_ID
Order By a.BASE_ID, Level Desc;
Tom
"Tom Cooper" <tomcoo...@xxxxxxxxxxx> wrote in message
news:uHh38tGHKHA.1988@xxxxxxxxxxxxxxxxxxxxxxx
> The following will get you the result you want before rotating the > table.
> You will need to do something like using dynamic SQL to rotate the > table.
> You cannot use PIVOT to rotate a table if the number of columns in the
> pivoted result is not known in advance.
> Create Table #employee (ID int, MGR_ID int);
> Insert #employee(ID, MGR_ID)
> Select 5, NULL
> Union All Select 6, 5
> Union All Select 10, 6
> Union All Select 15, NULL
> Union All Select 16, 15
> Union All Select 20, 16
> Union All Select 25, NULL
> Union All Select 26, 25
> Union All Select 30, 26;
> With EmployeeLevels As
> (Select ID As BASE_ID,
> -1 As InverseLevel,
> MGR_ID
> From #employee
> Where ID In (10, 20)
> Union All
> Select el.BASE_ID,
> el.InverseLevel - 1,
> e.MGR_ID
> From #employee e
> Inner Join EmployeeLevels el On el.MGR_ID = e.ID)
> Select BASE_ID,
> -1 * InverseLevel As Level,
> MGR_ID
> From EmployeeLevels
> Order By BASE_ID, Level Desc;
> go
> Drop Table #Employee;
> Tom
> "Michael Cortese" <mjc...@xxxxxxxxx> wrote in message
>news:f046428a-c6fe-4e0e-9f75-944f184496fa@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>I have used CTEs many times, including recursively. But I have always
>> done from the top down (base select has parentid=NULL). I now need to
>> go the other way.
>> For example:
>> TABLE employee
>> ID MGR_ID
>> 5 NULL
>> 6 5
>> 10 6
>> 15 NULL
>> 16 15
>> 20 16
>> 25 NULL
>> 26 25
>> 30 26
>> Basically, I need the CTE to return the managers above for a set of
>> IDs (they can be any level). They
>> need to be grouped someway to let me know that they are a group.
>> The list of Ids will come from another query that uses the CTE.
>> An example would be for IDs 10 and 20.
>> I would expect something like:
>> BASEID LVL MGR_ID
>> 10 3 6
>> 10 2 5
>> 10 1 NULL
>> 20 3 16
>> 20 2 15
>> 20 1 NULL
>> For my ultimate goal , I need to pivot this to get something like
>> this:
>> Baseid Lvl1_Mgr Lvl2_Mgr etc....
>> 10 6 5
>> 20 16 15
>> There can be many levels of managers, so I would need to use PIVOT
>> Any help appreciated
Tom, thanks for the response. However, I do not know the ID's in
advance and need to come from the base query, not the CTE:
Where ID In (10, 20)
needs to be outside of the CTE. That is my problem.
.
- References:
- Using a CTE for hierarchy data, from bottom up
- From: Michael Cortese
- Re: Using a CTE for hierarchy data, from bottom up
- From: Tom Cooper
- Re: Using a CTE for hierarchy data, from bottom up
- From: Tom Cooper
- Re: Using a CTE for hierarchy data, from bottom up
- From: Michael Cortese
- Using a CTE for hierarchy data, from bottom up
- Prev by Date: Re: the necessity of drop temp table code
- Next by Date: Re: the necessity of drop temp table code
- Previous by thread: Re: Using a CTE for hierarchy data, from bottom up
- Next by thread: Re: Using a CTE for hierarchy data, from bottom up
- Index(es):
Relevant Pages
|
