Versionshistorie für die Antwort #A1622 zur Frage #F436: MSSQL 2008 Hierarchie abbilden @CODEKICKER

Version 3

25.11.2009 11:10:11

Dies ist die aktuelle Version

Hab gestern und heute noch ein wenig rumexperimentiert und die Lösung gefunden.

/*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

Ergebnis:

Vieleicht hilft es ja jemanden anderes auch weiter.

Quellen:
Uncommon Common Table Expressions (CTEs) By Jeremy Likness

Floyd

10,4k ● 3 ● 9

Version 2

25.11.2009 11:10:11

Hab gestern und heute noch ein wenig rumexperimentiert und die Lösung gefunden.

/*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

Ergebnis:

Vieleicht hilft es ja jemanden anderes auch weiter.

Floyd

10,4k ● 3 ● 9

Version 1

25.11.2009 11:03:55

Hab gestern und heute noch ein wenig rumexperimentiert und die Lösung gefunden.

/*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

Vieleicht hilft es ja jemanden anderes auch weiter.

Floyd

10,4k ● 3 ● 9