Stücklisten Auflistung MSSQL

WITH t1 AS (
	SELECT	ART.PK_id AS ART_PK_id,
			ART.such AS ART_such,
			POS.FK_elex AS POS_FK_elex,
			ARG.nummer AS ARG_nummer
	FROM	dbo.Part$Product_2_1 ART
	LEFT JOIN Part$Product_2_1_TAB POS
	ON		ART.PK_id = POS.FK_id
	LEFT JOIN Operation$Operation_7_0 ARG
	ON		POS.FK_elex = ARG.PK_id
	), t2 AS (
	SELECT	t1.ART_PK_id AS Hauptartikel,
			t1.ART_PK_id,
			t1.ART_such,
			t1.POS_FK_elex,
			t1.ARG_nummer,
			cast(t1.ART_PK_id AS VARCHAR(MAX)) AS pfad
	FROM	t1
	WHERE	t1.ART_such = '2.8705.051.5'  
	UNION ALL
	SELECT	t2.Hauptartikel,
			t1.ART_PK_id,
			t1.ART_such,
			t1.POS_FK_elex,
			t1.ARG_nummer,
			cast(t2.pfad + '\' + t1.ART_PK_id AS VARCHAR(MAX)) AS pfad  
	FROM	t2
	INNER JOIN t1
	ON		t2.POS_FK_elex = t1.ART_PK_id
	)
SELECT	*
FROM	t2

WITH t1 AS (
	SELECT	ART.PK_id AS ART_PK_id,
			ART.such AS ART_such,
			POS.FK_elex AS POS_FK_elex,
			ARG.nummer AS ARG_nummer
	FROM	dbo.Part$Product_2_1 ART
	LEFT JOIN Part$Product_2_1_TAB POS
	ON		ART.PK_id = POS.FK_id
	LEFT JOIN Operation$Operation_7_0 ARG
	ON		POS.FK_elex = ARG.PK_id
	)
SELECT	*
FROM	t1

WITH t1 AS (
	SELECT	ART.PK_id AS ART_PK_id,
			ART.such AS ART_such,
			POS.FK_elex AS POS_FK_elex,
			ARG.nummer AS ARG_nummer
	FROM	dbo.Part$Product_2_1 ART
	LEFT JOIN Part$Product_2_1_TAB POS
	ON		ART.PK_id = POS.FK_id
	LEFT JOIN Operation$Operation_7_0 ARG
	ON		POS.FK_elex = ARG.PK_id
	), t2 AS (
	SELECT	t1.ART_PK_id AS Hauptartikel,
			t1.ART_PK_id,
			t1.ART_such,
			t1.POS_FK_elex,
			t1.ARG_nummer,
			cast(t1.ART_PK_id AS VARCHAR(MAX)) AS pfad
	FROM	t1
	WHERE	t1.ART_such = '2.8705.051.5'  
	)
SELECT	*
FROM	t2

WITH t1 AS (
	SELECT	ART.PK_id AS ART_PK_id,
			ART.such AS ART_such,
			POS.FK_elex AS POS_FK_elex,
			ARG.nummer AS ARG_nummer
	FROM	dbo.Part$Product_2_1 ART
	LEFT JOIN Part$Product_2_1_TAB POS
	ON		ART.PK_id = POS.FK_id
	LEFT JOIN Operation$Operation_7_0 ARG
	ON		POS.FK_elex = ARG.PK_id
	), t2 AS (
	SELECT	t1.ART_PK_id AS Hauptartikel,
			t1.ART_PK_id,
			t1.ART_such,
			t1.POS_FK_elex,
			t1.ARG_nummer,
			cast(t1.ART_PK_id AS VARCHAR(MAX)) AS pfad
	FROM	t1
	WHERE	t1.ART_such = '2.8705.051.5'  
	UNION ALL
	SELECT	t2.Hauptartikel,
			t1.ART_PK_id,
			t1.ART_such,
			t1.POS_FK_elex,
			t1.ARG_nummer,
			cast(t2.pfad + '\' + cast(t1.ART_PK_id AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS pfad  
	FROM	t2
	INNER JOIN t1
	ON		t2.POS_FK_elex = t1.ART_PK_id
	)
SELECT	*
FROM	t2

WITH t1 AS (
	SELECT	
			CAST(ART.PK_id AS CHAR(30))ART_PK_id,
			ART.such AS ART_such,
			ART.bsart AS ART1_bsart,
			POS.FK_elex AS POS_FK_elex,
			ART.MANDANT_ID AS ART_MANDANT_ID,
			CASE WHEN ART1.such <> '' THEN ART1.such WHEN ARG.such <> '' THEN ARG.such END AS Position  
	FROM	dbo.Part$Product_2_1 ART
	LEFT JOIN Part$Product_2_1_TAB POS
	ON		ART.PK_id = POS.FK_id
	LEFT JOIN Operation$Operation_7_0 ARG
	ON		POS.FK_elex = ARG.PK_id
	LEFT JOIN dbo.Part$Product_2_1 ART1 ON POS.FK_elex = ART1.PK_id
	), t2 AS (
	SELECT
			1 AS Ebene,
			t1.ART_PK_id AS Hauptartikel,
			CAST(NULL AS CHAR(30)) AS Hauptbaugruppe,
			CAST(t1.ART_PK_id AS CHAR(30))ART_PK_id,
			t1.ART_such AS baugruppe,
			t1.ART1_bsart,
			CAST(t1.POS_FK_elex AS CHAR(30))POS_FK_elex,
			t1.ART_MANDANT_ID,
			t1.Position,
			cast(t1.ART_PK_id AS VARCHAR(MAX)) AS pfad
	FROM	t1
	WHERE	t1.ART_MANDANT_ID = 3 and t1.ART_such ='2.2421.001.5'  
	UNION ALL
	SELECT	t2.Ebene + 1,
			t2.Hauptartikel,
			(CASE WHEN t2.Ebene + 1 = 2 THEN CAST(t1.ART_PK_id AS CHAR(30)) ELSE t2.Hauptbaugruppe END),
			CAST(t1.ART_PK_id AS CHAR(30))ART_PK_id,
			t1.ART_such,
			t1.ART1_bsart,
			CAST(t1.POS_FK_elex AS CHAR(30))POS_FK_elex,
			t1.ART_MANDANT_ID,
			t1.Position,
			cast(t2.pfad + '\' + cast(t1.ART_PK_id AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS pfad  
	FROM	t2
	INNER JOIN t1
	ON		t2.POS_FK_elex = t1.ART_PK_id
	)
SELECT	*
FROM	t2;