/*Temptabelle löschen die für diese Beispiel gebraucht wird*/
IF OBJECT_ID('tempdb..#T') IS NOT NULL
	drop table #T
go

/*Temporäre Tabellen erstellen als Beispiel für die echten Datentabellen*/
IF OBJECT_ID('tempdb..#Items') IS NOT NULL
	drop table #Items
IF OBJECT_ID('tempdb..#ItemRelation') IS NOT NULL
	drop table #ItemRelation
go

create table #ItemRelation(
	ItemRelationID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
	ChildItemID int,
	ParentItemID  int,
	Menge int
)
create table #Items(
	ItemID int ,
	Name [varchar](100),
	PreisProVPE money
)

/*Die Datentabellen befüllen*/
Insert into #Items VALUES (1,'Werkzeugkoffer',null)
Insert into #Items VALUES (2,'CCC',null)
Insert into #Items VALUES (3,'Schraubendeher X1',null)
Insert into #Items VALUES (9,'Schraubendeher X2',null)
Insert into #Items VALUES (4,'AAA',null)
Insert into #Items VALUES (5,'BBB',null)
Insert into #Items VALUES (7,'PVC',30)
Insert into #Items VALUES (8,'Aluminium',23)

Insert into #ItemRelation VALUES (1,3,1)
Insert into #ItemRelation VALUES (1,9,1)
Insert into #ItemRelation VALUES (2,4,1)
Insert into #ItemRelation VALUES (2,5,1)
Insert into #ItemRelation VALUES (3,7,2)
Insert into #ItemRelation VALUES (3,8,5)
Insert into #ItemRelation VALUES (9,7,1)
Insert into #ItemRelation VALUES (9,8,6)
go

/*Verwendung von Common Table Expressions*/
with Rec(ItemID,Name,PreisProVPE,Menge) 
 as (
  /*Das 1. Select bezieht sich auf das oberste 
    Element desen Kinder ich recursive Zusammenfassen will*/
  select
    ItemID,
    Name,
    PreisProVPE,
    1 Menge
  from #Items
  where ItemID =1 --1 = Werkzeugkoffer

  /*Recursiver Select aller Unter-Artikel*/
  union all 
  select
    L.ParentItemID as ItemID, 
    CAT.Name,
		CAT.PreisProVPE,
		L.Menge
  from #ItemRelation L 
  join Rec c 
    on c.ItemID = L.ChildItemID
  inner join #Items CAT 
    on L.ParentItemID = CAT.ItemID
)
/*Das Ergebnis in eine Temptabelle kopieren
	Die Common Table ist nach der ersten Verwendung nicht mehr Vorhanden*/
select * Into #T from Rec

/*Verarbeiten der Daten*/
select * from #T --Liste der Artikel mit Menge und Preis erstellen
select 
  ItemID,Name,PreisProVPE,
  Sum(Menge) GesammtMengeProWerkstoff,PreisProVPE * Sum(Menge) PreisProWerkstoff 
	from #T
	group by ItemID,Name,PreisProVPE --Preis und Menge pro Werkstoff
select sum(PreisProVPE*Menge) [Gesamtpreise] from #T	--Gesamtpreise aller Werkstoffe