CODEWITHSUNDEEP
sql-serverrecursioncommon-table-expression
Sheng Liu
Sheng Liu

Reputation: 79

Using Recursive CTE with GroupBy

I am new to the recursive CTE concept and a problem at hand, I got a tiny feeling that the problem can be solved by using recursive CTE. Let me know what you guys think.

Two tables:

Table one is a self referencing Location table with ID, ParentID, Level and Description.

Table two is an asset table which records individual assets and has a foreign key to Location table ID field.

Table1:
ID   Description ParentID  Level
1    Site1       NULL      1
2    Site2       NULL      1
3    Building1   1         2
4    Building2   1         2
5    Floor1      3         3
6    Floor2      3         3
7    Floor3      4         3
8    Place1      5         4
9    Place2      7         4

Table2:
ID  Description  Quantity  LocationID
1   Desk         3         8
2   Lamp         1         8
3   PC           10        9

I would like to create a stored procedure with a input parameter of @Level and returns all the Location records at that level and the number of assets within the location (including sub levels).

For example, if @Level = 3, the stored procedure should return:

ID  Description  AssetCount
5   Floor1       4   
6   Floor2       0
7   Floor3       10

If @Level = 2, the stored procedure should return:

ID Description AssetCount
3  Building1   4    
4  Building2   10

If the problem is not clear, please let me know.

Upvotes: 2

Views: 593

Answers (1)

James Z
James Z

Reputation: 12317

Well, nothing special here, just a recursive CTE joined with the other table, and the results are what you expected:

declare @level int = 3

;with CTE as (
  select id as origid, id, Description, parentid
  from table1 where level = @level
union all
  select CTE.origid, t1.id, CTE.Description, t1.parentid
  from CTE join table1 t1 on
  CTE.id = t1.parentid
)

select origid, CTE.description, isnull(sum(t2.Quantity),0) as Quantity
from CTE left outer join table2 t2 on CTE.id = t2.locationid
group by origid, CTE.description

SQL Fiddle

Upvotes: 3

Related Questions