FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

select @@version;

CREATE TABLE T1 ( PRTNO VARCHAR(50), HighLevelAssembly VARCHAR(50), QuantityPerArticle INT );

-- DML to insert the provided data

INSERT INTO T1 (PRTNO, HighLevelAssembly, QuantityPerArticle) VALUES ('21-1245-00', '841-038269-793', 1), ('21-1245-00', '841-133133-002', 1), ('21-1245-00', '841-038269-927', 1), ('21-1245-00', '841-A90940-793', 1), ('21-1245-00', '841-038269-819', 1), ('21-1245-00', '841-133133-003', 1), ('841-133133-003', '51-135432-002', 1), ('51-135432-002', '82-1014-823', 1), ('82-1014-823', '52-10154-7', 1), ('52-10154-7', '84-2526-100', 1), ('52-10154-7', '84-3421-132', 1), ('84-2526-100', '43-1246-01', 1), ('43-1246-01', '572-12126-500', 1), ('572-12126-500', '572-12126-500', 1), ('84-3421-132', '32-9567-8912', 1), ('32-9567-8912', '572-12126-500', 1), ('572-12126-500', '572-12126-500', 1);
/*select
	*
from 
	T1 level0
where 
	level0.prtno = '21-1245-00'; 
    
WITH chain AS (
  -- seed: start at the chosen root assembly
  SELECT 
      t.HighLevelAssembly AS root,
      t.PRTNO             AS node,
      CAST('|' + t.HighLevelAssembly + '|' + t.PRTNO + '|' AS VARCHAR(MAX)) AS path
  FROM T1 AS t
  WHERE t.HighLevelAssembly = '21-1245-00'

UNION ALL

-- recurse: current node becomes the next level's HighLevelAssembly
  SELECT 
      c.root,
      t.PRTNO,
      c.path + t.PRTNO + '|'
  FROM chain AS c
  JOIN T1    AS t
    ON t.HighLevelAssembly = c.node
  -- cycle guard: don’t revisit a node already in the path
  WHERE c.path NOT LIKE '%|' + t.PRTNO + '|%'
)
-- keep only leaves: nodes that never appear as a HighLevelAssembly
SELECT 
    c.root AS HighLevelAssembly,
    c.node AS PRTNO
FROM chain AS c
LEFT JOIN T1 AS t
  ON t.HighLevelAssembly = c.node
WHERE t.HighLevelAssembly IS NULL
ORDER BY c.node
OPTION (MAXRECURSION 32767);  -- raise if your hierarchies are deep
*/
WITH chain AS (

-- Anchor member: start at the root assembly

SELECT

CAST(t.HighLevelAssembly AS VARCHAR(MAX)) AS root,

CAST(t.PRTNO AS VARCHAR(MAX)) AS node,

CAST(t.HighLevelAssembly + ',' + t.PRTNO AS VARCHAR(MAX)) AS path

FROM T1 t

WHERE t.PRTNO = '21-1245-00'

UNION ALL

-- Recursive member: walk downward

SELECT

c.root,

CAST(t.PRTNO AS VARCHAR(MAX)) AS node,

CAST(c.path + ',' + t.PRTNO AS VARCHAR(MAX)) AS path

FROM chain c

JOIN T1 t

ON t.HighLevelAssembly = c.node

WHERE CHARINDEX(t.PRTNO, c.path) = 0 -- prevent cycles

)

-- Select only leaves: nodes that never appear as HighLevelAssembly

SELECT

c.node AS PRTNO,

c.root AS HighLevelAssembly

FROM chain c

LEFT JOIN T1 t

ON t.HighLevelAssembly = c.node

WHERE t.HighLevelAssembly IS NULL

ORDER BY c.node;

Col_0
Microsoft SQL Server 2022 (RTM-CU20-GDR) (KB5063814) - 16.0.4210.1 (X64)
Jul 11 2025 13:04:02
Copyright (C) 2022 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 22.04.5 LTS)

PRTNO	HighLevelAssembly
21-1245-00	841-038269-793
21-1245-00	841-133133-002
21-1245-00	841-038269-927
21-1245-00	841-A90940-793
21-1245-00	841-038269-819
21-1245-00	841-133133-003

(1 rows affected)

(17 rows affected)

(6 rows affected)