QUERY FORSE IMPOSSIBILE?

USE tempdb;

CREATE TABLE dbo.TBL_CATEGORIA(
ID int NOT NULL,
CATEGORIA varchar(20) NOT NULL,
ID_CAT_PADRE int NULL,
CONSTRAINT PK_CATEGORIA PRIMARY KEY(ID),
CONSTRAINT FK_CATEGORIA_ID_CAT_PADRE_ID 
FOREIGN KEY(ID_CAT_PADRE)
REFERENCES dbo.TBL_CATEGORIA(ID)
);

INSERT dbo.TBL_CATEGORIA VALUES(0, 'CATEGORIE', NULL);
INSERT dbo.TBL_CATEGORIA VALUES(1, 'FERRAMENTA', 0);
INSERT dbo.TBL_CATEGORIA VALUES(2, 'VITI', 1);
INSERT dbo.TBL_CATEGORIA VALUES(3, 'VARIE', 0);
INSERT dbo.TBL_CATEGORIA VALUES(4, 'BULLONI', 1);
INSERT dbo.TBL_CATEGORIA VALUES(5, 'VITI A CROCE', 2);
GO

CREATE FUNCTION dbo.ufn_GetSubtree(
@ID_CAT_PADRE AS int
)
RETURNS @tree table(
ID int NOT NULL,
ID_CAT_PADRE int NULL,
CATEGORIA varchar(20) NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
    DECLARE @lvl AS int
    SELECT @lvl = 0

    INSERT INTO @tree
    SELECT ID, ID_CAT_PADRE, CATEGORIA,
           @lvl, '.' + CAST(ID AS varchar(10)) + '.'
    FROM dbo.TBL_CATEGORIA
    WHERE ID = @ID_CAT_PADRE

    WHILE @@ROWCOUNT > 0
    BEGIN
        SET @lvl = @lvl + 1

        INSERT INTO @tree
        SELECT C.ID, C.ID_CAT_PADRE, C.CATEGORIA,
               @lvl, T.path + CAST(C.ID AS varchar(10)) + '.'
        FROM dbo.TBL_CATEGORIA AS C JOIN @tree AS T
        ON C.ID_CAT_PADRE = T.ID 
        AND T.lvl = @lvl - 1
    END  
    RETURN
END
GO

SELECT REPLICATE ('|-', lvl) + CATEGORIA AS CATEGORIA
FROM ufn_GetSubtree(0)
ORDER BY path;

/* Output:

CATEGORIA
-------------------
CATEGORIE
|-FERRAMENTA
|-|-VITI
|-|-|-VITI A CROCE
|-|-BULLONI
|-VARIE

(6 row(s) affected)

*/

DROP FUNCTION dbo.ufn_GetSubtree;
DROP TABLE dbo.TBL_CATEGORIA;

USE tempdb;
GO

CREATE TABLE dbo.TBL_CATEGORIA
(
    ID int NOT NULL
  , Categoria varchar(30) NOT NULL
  , IDPadre int NULL
  , CONSTRAINT PK_TBL_CATEGORIA PRIMARY KEY CLUSTERED
  (
    ID
  )
)
GO

INSERT INTO dbo.TBL_CATEGORIA (ID, Categoria, IDPadre)
VALUES (1, 'TUTTE', NULL)
INSERT INTO dbo.TBL_CATEGORIA (ID, Categoria, IDPadre)
VALUES (2, 'FERRAMENTA', 1)
INSERT INTO dbo.TBL_CATEGORIA (ID, Categoria, IDPadre)
VALUES (3, 'VITI', 2)
INSERT INTO dbo.TBL_CATEGORIA (ID, Categoria, IDPadre)
VALUES (4, 'VARIE', 1)
INSERT INTO dbo.TBL_CATEGORIA (ID, Categoria, IDPadre)
VALUES (5, 'BULLONI', 2)
INSERT INTO dbo.TBL_CATEGORIA (ID, Categoria, IDPadre)
VALUES (6, 'VITI A CROCE', 3)
GO

ALTER TABLE dbo.TBL_CATEGORIA 
ADD CONSTRAINT FK_TBL_CATEGORIA_TBL_CATEGORIA 
FOREIGN KEY (IDPadre) 
REFERENCES dbo.TBL_CATEGORIA (ID)
GO

;WITH Categorie (ID, Categoria, IDPadre, Livello, Ordine) AS 
(
  SELECT
      ID
    , Categoria
    , IDPadre
    , Livello = 0
    , Ordine = CAST(ID AS varchar(10))
  FROM 
    dbo.TBL_CATEGORIA
  WHERE 
    IDPadre IS NULL

  UNION ALL

  SELECT
       C.ID
    , C.Categoria
    , C.IDPadre
    , Livello = CT.Livello + 1
    , Ordine = CAST(Ordine + CAST(C.ID AS varchar(10)) AS varchar(10))
  FROM 
    dbo.TBL_CATEGORIA    AS C
    JOIN Categorie      AS CT  ON C.IDPadre = CT.ID
)

SELECT 
  cast(REPLICATE('|- ', Livello) + Categoria AS varchar(50)) 
FROM 
  Categorie
ORDER BY Ordine

/*
--------------------------------------------------
TUTTE
|- FERRAMENTA
|- |- VITI
|- |- |- VITI A CROCE
|- |- BULLONI
|- VARIE
*/