CREATE TABLE GOOD ( --товары
      ID Integer NOT NULL, --первичный ключ
      LEFTBOUND Integer NOT NULL,
      RIGHTBOUND Integer NOT NULL,
      PARENT Integer,--сылка на группу владельца, для первого уровня NULL
      NAME Varchar(50) NOT NULL, --наименование группы или товара
      ISGROUP Integer NOT NULL --признак группы: 0,1
);

SELECT g.ID, g.Name, g.ISGROUP, count(g2.ID) AS glevel
FROM GOOD g
LEFT JOIN GOOD g2 ON (g.LEFTBOUND BETWEEN g2.LEFTBOUND AND g2.RIGHTBOUND)
GROUP BY g.ID, g.Name, g.ISGROUP, g.LEFTBOUND
ORDER BY g.LEFTBOUND

SELECT 
T.ID1 ID ,T.NAME1 NAME, COUNT(DISTINCT GLEV.ID) LEV
FROM
(SELECT G1.ID ID1, G2.ID ID2, MAX(MP.LEFTBOUND) MPL, MIN(MP.RIGHTBOUND) MPR, G1.LEFTBOUND GL1, G2.LEFTBOUND GL2, G1.NAME NAME1 
FROM GOOD G1
LEFT JOIN GOOD G2 ON NOT(G1.ID=G2.ID)
LEFT JOIN GOOD MP ON ((G1.LEFTBOUND BETWEEN MP.LEFTBOUND AND MP.RIGHTBOUND) AND (G2.LEFTBOUND BETWEEN MP.LEFTBOUND AND MP.RIGHTBOUND))
GROUP BY G1.ID, G2.ID, G1.LEFTBOUND, G2.LEFTBOUND, G1.NAME) T 
LEFT JOIN GOOD MP0 ON (MP0.LEFTBOUND = T.MPL)
LEFT JOIN GOOD GI1 ON ((GI1.PARENT = MP0.ID) OR ((MP0.ID IS NULL) AND (GI1.PARENT IS NULL))) AND (T.GL1 BETWEEN GI1.LEFTBOUND AND GI1.RIGHTBOUND)
LEFT JOIN GOOD GI2 ON ((GI2.PARENT = MP0.ID) OR ((MP0.ID IS NULL) AND (GI2.PARENT IS NULL))) AND (T.GL2 BETWEEN GI2.LEFTBOUND AND GI2.RIGHTBOUND)
LEFT JOIN GOOD GLEV ON (T.GL1 BETWEEN GLEV.LEFTBOUND AND GLEV.RIGHTBOUND) 
GROUP BY T.ID1, T.NAME1
ORDER BY
      SUM(
      CASE
        WHEN T.ID1=MP0.ID THEN 1
        WHEN T.ID2=MP0.ID THEN 0
        WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN 1
        WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN 0        
        WHEN (GI1.NAME<GI2.NAME) THEN 1        
        ELSE 0
      END) DESC

ORDER BY
     COUNT( DISTINCT
     CASE
       WHEN T.ID1=MP0.ID THEN ID2
       WHEN T.ID2=MP0.ID THEN NULL
       WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN ID2
       WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN NULL        
       WHEN (GI1.NAME<GI2.NAME) THEN ID2        
       ELSE NULL
     END)

SELECT 
G1.ID ID ,G1.NAME NAME, 
      SUM(
      CASE WHEN G1.LEFTBOUND BETWEEN G2.LEFTBOUND AND G2.RIGHTBOUND THEN 1
           ELSE 0
      END) LEV
FROM GOOD G1
LEFT JOIN GOOD G2 ON NOT(G1.ID=G2.ID)
LEFT JOIN GOOD GI1 ON (G1.LEFTBOUND BETWEEN GI1.LEFTBOUND AND GI1.RIGHTBOUND)
LEFT JOIN GOOD GI2 ON (G2.LEFTBOUND BETWEEN GI2.LEFTBOUND AND GI2.RIGHTBOUND)
WHERE
     ((GI1.PARENT=GI2.PARENT) OR ((GI1.PARENT IS NULL)AND(GI2.PARENT IS NULL))) AND
     (NOT(GI1.ID=GI2.ID) OR (G1.ID=GI1.ID) OR (G2.ID=GI2.ID))   
GROUP BY G1.ID, G1.NAME
ORDER BY
     SUM( 
     CASE
       WHEN G2.LEFTBOUND BETWEEN G1.LEFTBOUND AND G1.RIGHTBOUND THEN 1
       WHEN G1.LEFTBOUND BETWEEN G2.LEFTBOUND AND G2.RIGHTBOUND THEN 0
       WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN 1
       WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN 0        
       WHEN (GI1.NAME<GI2.NAME) THEN 1        
       ELSE 0
     END) DESC

CREATE TABLE SALE (
       ID Integer NOT NULL,
       GOOD Integer NOT NULL,
       ACOUNT Numeric(15,3) NOT NULL,
       CONSTRAINT PK_SALE PRIMARY KEY (ID)
);
ALTER TABLE SALE ADD CONSTRAINT FK_SALE_1 FOREIGN KEY (GOOD) REFERENCES GOOD(ID);

SELECT T.ID ID ,T.NAME NAME, T.LEV LEV, SUM(S.ACOUNT) ACOUNT
FROM SALE S
INNER JOIN GOOD G ON (S.GOOD=G.ID)
INNER JOIN
(SELECT 
G1.ID ID ,G1.NAME NAME, G1.LEFTBOUND LEFTBOUND, G1.RIGHTBOUND RIGHTBOUND, 
      SUM(
      CASE WHEN G1.LEFTBOUND BETWEEN G2.LEFTBOUND AND G2.RIGHTBOUND THEN 1
           ELSE 0
      END) LEV,
     SUM( 
     CASE
       WHEN G2.LEFTBOUND BETWEEN G1.LEFTBOUND AND G1.RIGHTBOUND THEN 1
       WHEN G1.LEFTBOUND BETWEEN G2.LEFTBOUND AND G2.RIGHTBOUND THEN 0
       WHEN (GI1.ISGROUP>GI2.ISGROUP) THEN 1
       WHEN (GI1.ISGROUP<GI2.ISGROUP) THEN 0        
       WHEN (GI1.NAME<GI2.NAME) THEN 1        
       ELSE 0
     END) ORD
FROM GOOD G1
LEFT JOIN GOOD G2 ON NOT(G1.ID=G2.ID)
LEFT JOIN GOOD GI1 ON (G1.LEFTBOUND BETWEEN GI1.LEFTBOUND AND GI1.RIGHTBOUND)
LEFT JOIN GOOD GI2 ON (G2.LEFTBOUND BETWEEN GI2.LEFTBOUND AND GI2.RIGHTBOUND)
WHERE
     ((GI1.PARENT=GI2.PARENT) OR ((GI1.PARENT IS NULL)AND(GI2.PARENT IS NULL))) AND
     (NOT(GI1.ID=GI2.ID) OR (G1.ID=GI1.ID) OR (G2.ID=GI2.ID))   
GROUP BY G1.ID, G1.NAME, G1.LEFTBOUND, G1.RIGHTBOUND) T 
      ON ((G.ID=T.ID) OR (G.LEFTBOUND BETWEEN T.LEFTBOUND AND T.RIGHTBOUND))
GROUP BY T.ID,T.NAME,T.LEV, T.ORD      
ORDER BY T.ORD DESC

SELECT g.ID, g.Name, g.ISGROUP, count(g2.ID) AS glevel
N>FROM GOOD g
N>LEFT JOIN GOOD g2 ON (g.LEFTBOUND BETWEEN g2.LEFTBOUND AND g2.RIGHTBOUND)
N>GROUP BY g.ID, g.Name, g.ISGROUP, g.LEFTBOUND
N>ORDER BY g.LEFTBOUND

create function dbo.fn_get_all_custom_hierarchy_nodes_recursive(@hierarchy_id int, @level int)
returns table
as
return
with tree ([id], [level], [name], [parent_id], [top_node_id], [description], path_symbolic, path_numeric) as (
    select    [id]
            , [level]
            , [name]
            , [parent_id]
            , [id] as [top_node_id]
            , [description]
            , convert(nvarchar(max), [name]) as path_symbolic
            , convert(nvarchar(max), [id]) as path_numeric
    from        dbo.custom_hierarchy_nodes
    where        [hierarchy_id] = @hierarchy_id
                and [level] = @level

    union all

    select    n.[id]
            , n.[level]
            , n.[name]
            , n.[parent_id]
            , t.[top_node_id]
            , n.[description]
            , t.path_symbolic + N'/' + n.[name] as path_symbolic
            , t.path_numeric + N'/' + convert(nvarchar(max), n.[id]) as path_numeric
    from        dbo.custom_hierarchy_nodes n
            , tree t
    where        n.parent_id = t.[id]
)
select        id
            , [level]
            , [name]
            , [parent_id]
            , [top_node_id]
            , [description]
            , path_symbolic
            , path_numeric
from        tree;

select        *
from        dbo.fn_get_all_custom_hierarchy_nodes_recursive(1,1)
order by    [level]
            , parent_id
            , [name]