Kategorisierte Struktur sortieren (stored procedure/Cursor)

[siles]

Hallo!
Mein Gehirn ist gerade ziemlich abgeraucht, vermutlich wäre es gar nicht soo schwer aber ich komme nicht darauf :)
Problem:

Ich habe eine Kategorien Datenbank (id, name, pid). Wird normalerweise in Delphi mit einer TreeView angezeigt.
Nun möchte ich jedoch alle Kategorien in eine Combobox bringen und zwar schön in der Reihenfolge, wie sie auch in der TreeView angezeigt wird. Sprich:

Oberkategorie 1
--K1
----U1
--K2
-----U2
Oberkategorie 2
....etc

Bei der TreeView geht das vollautomatisch, da muss nur die ID und PID angegeben werden (DynamicDBTreeView)

Ich habe igrendwo im Internet, vielleicht sogar hier bei DP eine Stored Procedure gesehen, die jede Unterkategorie sortiert anzeigt:

SQL-Code:

CREATE Procedure ListSubTree (@cat_id int) 
as 

declare @ChildID int 
declare @Table Table ( 
  ID  INT, 
  PID INT, 
  NAME VARCHAR(1000) 
) 

-- 1.Zeile in die Tabelle
 
insert into @Table 
  select cat_id, cat_pid, cat_name from cate where cat_id = @cat_id  

Declare c Cursor local for select ID from @Table 
open c 
fetch next from c into @ChildID 
while @@Fetch_status = 0 begin 

-- Mit jedem Schleifendurchlauf werden in einem Abwasch ALLE Kindknoten eingefügt 

  insert into @Table 
    select cat_id, cat_pid, cat_name from cate where cat_pid = @ChildID
  fetch next from c into @ChildID 
end 
close c 
deallocate c 
select * from @Table
GO

Mit "exec listsubtree 1" bekomme ich dann auch alle Unterkategorien von der Kategorie mit der ID 1, allerdings nicht so sortiert wie oben beschrieben, sondern so

Oberkategorie 1
--K1
--K2
----U1
----U2
....etc

Hat einer ne Ahnung wie ich das erreichen kann?

[NormanNG]

Hi,

in der SP wird am Schluss die Ergebnismenge *unsortiert* zurückgegeben:

SQL-Code:

...
end
close c
deallocate c
select * from @Table
GO

Zu Sortieren etwas so oder ähnlich

SQL-Code:

...
select * from @Table order by PID, ID
GO

[siles]

Hi,
Schade, wäre schön gewesen wenn das so einfach geklappt hätte :(
Dachte zuerst auch dass es nur an einem "order" liegt, aber schau mal, so sieht das bei meinen Testdaten aus:

Delphi-Quellcode:

EXEC listsubtree 1


        ID      PID     Name
   1   0   Oberkategorie 1
   2   1   Kategorie 1
   3   1   Kategorie 2
   4   2   Unterkategorie A
   6   3   Unterkategorie B
   7   3   Unterkategorie C
   9   4   UnterUntere Kategorie
   17   9   Unterste Kategorie

Egal ob das am Ende nach ID oder PID sortiert wird, die Unterkategorie A kommt in beiden Fällen erst NACH der "Kategorie 2", sie sollte jedoch direkt nach der "Kategorie 1" erscheinen.

Das ganze ist aber glaube ich einfach zu schwer :(
Ich danke die aber trotzdem!

[NormanNG]

sorry, war Quatsch :drunken:

[alzaimar]

Du müsstest in der Tabelle eben eine totale Ordnung einführen. Das geht z.B. mit einem hierarchischen Schlüssel. Hier die rekursive Definition:

Code:

Key (Item) = Key(Item.Parent)+ FixedFormat(Item.ID);
Key (Nil)='';

Wichtig ist, das jede Ebene eine feste Stellenanzahl für die Formatierung der eigenen ID bekommt. Dann hätte dein Beispiel (mit einer festen Stellenanzahl von 2) folgende Schlüssel:

Code:

01     Oberkategorie 1 (die ID sei 1)
0101   --K1 (die ID von K1 sei 1)
010101 ----U1
0102   --K2
010201 -----U2
02     Oberkategorie 2
....etc

Du musst in der SP einfach nur die zusätzliche Spalte einfügen und dann diese Spalte entsprechend füllen:

SQL-Code:

CREATE Procedure ListSubTree (@cat_id int)
as

declare @ChildID int
declare @Table Table (
  ID  INT,
  PID INT,
  NAME VARCHAR(1000),
  uKEY VarChar(80) -- Schlüssel
)

-- 1.Zeile in die Tabelle

insert into @Table
  select cat_id, cat_pid, cat_name, 
   dbo.UDFFixedFormat(cat_id,9)  --- Festes 9-stelliges Format (musste selber basteln) 
    from cate where cat_id = @cat_id 

Declare c Cursor local for select ID from @Table
open c
fetch next from c into @ChildID
while @@Fetch_status = 0 begin

-- Mit jedem Schleifendurchlauf werden in einem Abwasch ALLE Kindknoten eingefügt

  insert into @Table
    select c.cat_id, 
   c.cat_pid, 
   c.cat_name,
        p.uKEY  + dbo.UDFFixedFormat(c.cat_id,9) -- Rekursiv aufgebautes, 3-stelliges Format
   from cate c join cate p on c.cat_pid = p.cat_id
  where c.cat_pid = @ChildID
  fetch next from c into @ChildID
end
close c
deallocate c
select * from @Table order by uKey

GO

Nicht getestet, müsste aber passen.

[NormanNG]

Hi,

ich hab auch noch´n Versuch:

SQL-Code:

CREATE Procedure ListSubTree (@cat_id int)
as

declare @ChildID int
declare @Table Table (
  pfad varchar(100),
  ID  INT,
  PID INT,
  NAME VARCHAR(1000)
)

-- 1.Zeile in die Tabelle

insert into @Table
  select cat_id, cat_id, cat_pid, cat_name from cate where cat_id = @cat_id 


Declare c Cursor local for select ID from @Table
open c
fetch next from c into @ChildID
 while @@Fetch_status = 0 begin

-- Mit jedem Schleifendurchlauf werden in einem Abwasch ALLE Kindknoten eingefügt

  insert into @Table
    select t.pfad + '-' + convert(varchar, cat_id), cat_id, cat_pid, cat_name 
      from cate c
      join @table t on t.id = c.cat_pid
      where cat_pid = @ChildID
  fetch next from c into @ChildID
 end
close c
deallocate c
select * from @Table order by pfad
GO

Nicht wirklich schön, geht aber...

[alzaimar]

Hmmm. Gleiche Idee, nur keine feste Stellenanzahl pro Ebene. Auch gut, besser sogar, weil die Stellenanzahl unwichtig wird.

[siles]

Hui also die Version von Norman funktioniert auf Anhieb!
Die von alzaimar würde bestimmt auch klappen, nur bin ich wohl noch zu unerfahren um sie richtig zu begreifen!

Vielen Dank auf jedenfall euch beiden, ich werde mir beide mal anschauen, damit komme ich auf jedenfall weiter :)

Vielen Dank nochmals, ihr seid klasse!

[alzaimar]

Äh, warte mal. Geht doch nicht.
Beispiel: 2 Knoten ebene 1 (ID = 1 und 10). Jeweils ein Kindknoten (ID 2 und 3). Die Pfade wären:

• 1
  1-2
  10
  10-3

Sortiert wäre das aber

• 1
  10
  10-3
  1-2

Und das stimmt nicht. Leider muss man die Lösung mit der festen Stellenanzahl nehmen (ich wusste doch, wieso ich das mal so machen musste).

@siles: Du musst nur eine UDF (User Defined Function) erstellen:

SQL-Code:

create function UDFFixedFormat (@Number int, @Digits int)  
RETURNS VarChar(20) AS  
BEGIN 
  declare @zero VarChar(10), @n int
set @zero = '0000000000'
set @n = case 
      when @Number < 10 then @Digits-1
      when @Number < 100 then @Digits-2
      when @Number < 1000 then @Digits-3
      when @Number < 10000 then @Digits-4
      when @Number < 100000 then @Digits-5
      when @Number < 1000000 then @Digits-6
      when @Number < 10000000 then @Digits-7
      when @Number < 100000000 then @Digits-8
      else @Digits-8
   end
set @n = case when @n<0 then 0 else @n end
return substring (@zero,1,@n) + cast (@Number as VarChar)
END

Und dann meinen Code ausführen. Sollte dann laufen

[siles]

Stimmt Alzaimar :(

Also ich habe es jetzt mit deiner SP versucht, leider erhalte ich dabei:

Server: Msg 207, Level 16, State 3, Procedure ListSubTree, Line 26
Invalid column name 'uKEY'.

Der Fehler müsste hier irgendwo liegen:

SQL-Code:

 insert into @Table
    select c.cat_id,
   c.cat_pid,
   c.cat_name,
        p.uKEY  + dbo.UDFFixedFormat(c.cat_id,9) -- Rekursiv aufgebautes, 3-stelliges Format
   from cate c join cate p on c.cat_pid = p.cat_id
  where c.cat_pid = @ChildID

Weil in der Tabelle cate habe ich ja kein Feld namens uKEY?!