albero categorie articolo sql

[GEGGI]

Buonasera , ho un problema , ho una tabella con delle categorie articolo e queste cartelle sono contatenate tra loro, solamente che non so come posso dire a sql server di mettere di contatenarle tra madre figli nipoti
metto la tabbella in modo da essere più chiaro
id_categoria descrizione idCategoriaMadre
---------------|--------------------------------|----------------------|
30 |diesel | NULL
31 |diesel_bimbo | 30
32 |diesel bimba | 30
33 |diesel bimbo giacchetto | 31
34 |diesel bimbo tshirt | 31
35 |diesel bimbo felpa | 31
36 |diesel bimba giacchetti | 32
37 |diesel bimba gonna | 32
38 |diesel bimba felpa | 32
-------------------------------------------------------------------------

io da questa tabella vorrei fare una queri che mi faccia vedere su tre campi i tre livelli delle categorie articolo
ho scritto questa query , che funziona per la prima categoria ma non so come andare avanti

use [dbnegozio]

go

with CatPriLiv as

(SELECT id as id_categoria,nome,descrizione,idCategoriaMadre

from CategoriaArticolo

where CategoriaArticolo.isAttivo = 1 and id >= 30
)
select CatPriLiv.id_categoria,CatPriLiv.descrizione ,CatPriLiv.idCategoriaMadre,
CategoriaArticolo.nome as categoriaprimolivello

from CatPriLiv

left join CategoriaArticolo on CatPriLiv.idCategoriaMadre = CategoriaArticolo.id
--------------------------------------------------
in questo modo ottengo
id_categoria descrizione idCategoriaMadre categoriaprimolivello
---------------|--------------------------------|----------------------|----------------------------
30 |diesel | NULL |NULL
31 |diesel_bimbo | 30 |diesel
32 |diesel bimba | 30 |diesel
33 |diesel bimbo giacchetto | 31 |diesel_bimbo
34 |diesel bimbo tshirt | 31 |diesel_bimbo
35 |diesel bimbo felpa | 31 |diesel_bimbo
36 |diesel bimba giacchetti | 32 |diesel bimba
37 |diesel bimba gonna | 32 | diesel bimba
38 |diesel bimba felpa | 32 |diesel bimba
----------------------------------------------------------------------------------------------------
adesso vorrei continuare per ricostruire l'albero cioè mettere unulteriore colonna dove c'è la categoria principale
id_categoria descrizione idCategoriaMadre categoriaprimolivello categoriaprincipale
---------------|--------------------------------|----------------------|----------------------------|---------------------|
30 |diesel | NULL |NULL |NULL
31 |diesel_bimbo | 30 |diesel |NULL
32 |diesel bimba | 30 |diesel |NULL
33 |diesel bimbo giacchetto | 31 |diesel_bimbo |diesel
34 |diesel bimbo tshirt | 31 |diesel_bimbo |diesel
35 |diesel bimbo felpa | 31 |diesel_bimbo |diesel
36 |diesel bimba giacchetti | 32 |diesel bimba |diesel
37 |diesel bimba gonna | 32 | diesel bimba |diesel
38 |diesel bimba felpa | 32 |diesel bimba |diesel
-----------------------------------------------------------------------------------------------------------------------------

scusate srà una richiesta stupida ma non riesco ad uscirne
grazie in anticipo e scusate se ho scritto boiate il linguaggio slq non è il mio forte

 

[marino51]

srà una richiesta stupida

tutt'altro che....
si tratta di gestire un "Hierarchical Data Model" (queste le parole se vuoi cercare documentazione)
l' ho sviluppato su sql server (non è solo farina del mio sacco), memorizzando due procedure che se chiamate forniscono i risultati nelle direzioni alto-basso e viceversa vedi figura,

la tabella di partenza è valida e se ti va bene il risultato, ti posto le due procedure da memorizzare

 

[GEGGI]

Intanto grazie per la risposta celere, si il risultato va bene forse se fosse possibile la descrizione della categoria invece che l'id (NodeSequenze) sarebbe perfetta e grazie per le parole chiave cosi studio un po'!

 

[marino51]

noto che hai usato l'id automatico come categoria, rispetto la scelta ma potrebbe essere un rischio nel futuro,
in ogni caso ti propongo una soluzione che "interfaccia" la tua tabella con le procedure (query) per la selezione

dovresti creare una view

CREATE VIEW [GLHierarchyV] AS
    SELECT
        id               as GLID,
        id               as GLCD,
        descrizione      as GLDesc,
        idCategoriaMadre as ParentGLCD
    FROM CategoriaArticolo
    WHERE isAttivo = 1

la view non ha impatto sul sistema, essendo di fatto solo un alias della tua tabella

quindi puoi memorizzare la procedura per la ricerca "drill down"

create function GLSubordinates_v1( @GLCD varchar(10) )
    returns table as return with GLSubnodes
        ( distance, GLID, GLCD, GLDesc, ParentGLCD, NodeSequence ) as

        ( select 0, h.GLID, h.GLCD, h.GLDesc, h.ParentGLCD, convert( varchar(255), ltrim(h.GLDesc)) as NodeSequence
              from [GLHierarchyV] h
              where h.GLCD = @GLCD
          union all
          select distance+1,  h.GLID, h.GLCD, h.GLDesc, h.ParentGLCD, convert( varchar(255), sn.NodeSequence+'/'+ltrim(h.GLDesc))
              from [GLHierarchyV] h
              inner join GLSubnodes sn on h.ParentGLCD = sn.GLCD )
    select distance, GLID, GLCD, GLDesc, ParentGLCD, NodeSequence
        from GLSubnodes

poi la procedura per la ricerca inversa

create function GLSuperiors_v1( @GLCD varchar(10) )
    returns table as return with GLSupnodes
        ( distance, GLID, GLCD, GLDesc, ParentGLCD, ParentGLDesc ) as

        ( select 0, h.GLID, h.GLCD, h.GLDesc, h.ParentGLCD,
              ( select d.GLDesc from [GLHierarchyV] d where d.GLCD = h.ParentGLCD ) ParentGLDesc
              from [GLHierarchyV] h
              where h.GLCD = @GLCD
          union all
          select distance-1, h.GLID, h.GLCD, h.GLDesc, h.ParentGLCD,
              ( select d.GLDesc from [GLHierarchyV] d where d.GLCD = h.ParentGLCD ) ParentGLDesc
              from [GLHierarchyV] h
              inner join GLSupnodes sn on h.GLCD = sn.ParentGLCD )
    select distance, GLID, GLCD, GLDesc, ParentGLCD, ParentGLDesc
        from GLSupnodes

puoi richiamare le procedure molto semplicemente,

SELECT * FROM GLSubordinates_v1('30')

SELECT * FROM GLSuperiors_v1('38')

questo il risultato,

fai sapere

 

[marino51]

volendo puoi aggiungere opzioni di ordinamento

SELECT * FROM GLSubordinates_v1('30') order by NodeSequence

se necessitano nomi di colonne specifici, si possono elencare come alias sostituendo nella "SELECT *"