子查询在不同列中的递归查询

Question

我有一个递归表：

CREATE TABLE [dbo].[CATEGORIE](
            [id_categorie] [int] IDENTITY(1,1) NOT NULL,
            [Nom] [varchar](50) NOT NULL,
            [_Description] [varchar](2048) NULL,
            [id_categorie_1] [int] NULL,
            [id_codesActivite] [int] NULL,
    PRIMARY KEY CLUSTERED 
    (
        [id_categorie] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

ALTER TABLE [dbo].[CATEGORIE]  WITH CHECK ADD  CONSTRAINT 
[FK_CATEGORIE_id_categorie_1] FOREIGN KEY([id_categorie_1])
REFERENCES [dbo].[CATEGORIE] ([id_categorie])
GO

我想要一个查询，结果是一个表，所有的孩子都在不同的列中，如下所示：

COL1 | COL2    | COL3
----------------------------
CAT1 |         |
CAT1 | SCAT1-1 |
CAT1 | SCAT1-1 | SSCAT1-1-1
CAT1 | SCAT1-1 | SSCAT1-1-2
CAT1 | SCAT1-1 | SSCAT1-1-3
CAT1 | SCAT1-2 |
CAT1 | SCAT1-2 | SSCAT1-2-1
CAT1 | SCAT1-2 | SSCAT1-2-2
CAT1 | SCAT1-2 | SSCAT1-2-3
CAT2 |         |
CAT2 | SCAT2-1 | 
CAT2 | SCAT2-2 | 
CAT3 |         |
CAT3 | SCAT3-1 | 
CAT3 | SCAT3-1 | SSCAT3-1-1
CAT3 | SCAT3-1 | SSCAT3-1-2

我想我必须使用递归查询，就像在这个链接https://sqlpro.developpez.com/cours/sqlserver/cte-recursives/中一样

但我找不到好的。

谢谢

编辑：

目前，我有一个包含以下代码的视图：

SELECT
  (SELECT Nom
   FROM dbo.CATEGORIE AS CAT3
   WHERE (id_categorie =
        (SELECT id_categorie_1
         FROM dbo.CATEGORIE AS CAT2
         WHERE (id_categorie = dbo.CATEGORIE.id_categorie_1)))) AS CAT1,

  (SELECT Nom
   FROM dbo.CATEGORIE AS CAT2
   WHERE (id_categorie = dbo.CATEGORIE.id_categorie_1)) AS CAT2,
   Nom AS CAT3,
   id_categorie
FROM dbo.CATEGORIE

这是工作，但我需要“移位”C#代码中的列

+------+--------+----------+--------------+
| CAT1 | CAT2   | CAT3     | id_categorie |
+------+--------+----------+--------------+
| NULL | NULL   | CAT1     | 392          |
+------+--------+----------+--------------+
| NULL | CAT1   | CAT1-1   | 393          |
+------+--------+----------+--------------+
| NULL | CAT1   | CAT1-2   | 394          |
+------+--------+----------+--------------+
| NULL | CAT1   | CAT1-3   | 395          |
+------+--------+----------+--------------+
| CAT1 | CAT1-1 | CAT1-1-1 | 396          |
+------+--------+----------+--------------+
| CAT1 | CAT1-1 | CAT1-1-2 | 397          |
+------+--------+----------+--------------+
| CAT1 | CAT1-2 | CAT1-2-1 | 398          |
+------+--------+----------+--------------+
| NULL | NULL   | CAT2     | 399          |
+------+--------+----------+--------------+
| NULL | NULL   | CAT3     | 400          |
+------+--------+----------+--------------+
| NULL | CAT3   | CAT3-1   | 401          |
+------+--------+----------+--------------+
| NULL | CAT3   | CAT3-2   | 402          |
+------+--------+----------+--------------+
| CAT3 | CAT3-2 | CAT3-2-1 | 403          |
+------+--------+----------+--------------+
| CAT3 | CAT3-2 | CAT3-2-2 | 404          |
+------+--------+----------+--------------+
| CAT3 | CAT3-2 | CAT3-2-3 | 405          |
+------+--------+----------+--------------+