SQL查询结果重排：物料清单(BOM)层级化展示需求

Got it, let's tackle this BOM ordering problem first—since you want child assemblies to immediately follow their parent entries in the results, the key is to track the hierarchy path in your recursive CTE. That way, we can sort by this path to get the nested order you need.

Modified Recursive CTE with Hierarchy Sorting #

WITH bom (bomItem, partId, btmlvl, hierarchy_path) AS (
    -- Anchor member: start with the root assembly
    SELECT 
        [bomItem], 
        [partId], 
        [btmlvl],
        -- Initialize path with the root assembly ID (cast to VARCHAR for string concatenation)
        CAST([bomItem] AS VARCHAR(MAX)) AS hierarchy_path
    FROM [TESTDB].[dbo].[BOMTABLE] 
    WHERE [bomItem] = 'PART# GOES HERE, PASSED IN BY THE APP'

    UNION ALL

    -- Recursive member: get child parts/assemblies
    SELECT 
        subQuery.bomItem, 
        subQuery.partId, 
        subQuery.btmlvl,
        -- Append current part ID to the parent's hierarchy path
        mainQuery.hierarchy_path + '>' + CAST(subQuery.partId AS VARCHAR(MAX)) AS hierarchy_path
    FROM [TESTDB].[dbo].[BOMTABLE] AS subQuery
    INNER JOIN bom AS mainQuery 
        ON subQuery.bomItem = mainQuery.partId
)
-- Select the fields you need, sorted by the hierarchy path
SELECT [bomItem], [partId], [btmlvl]
FROM bom
ORDER BY hierarchy_path;

Why This Works #

• The hierarchy_path field creates a string like 1, 1>4, 1>4>17, 1>5, 1>8, 1>8>12, etc. When sorted alphabetically, this string preserves the nested order—child entries directly follow their parent.
• For your sample data, this query will return exactly the ordered result you requested:

  bomItem partId btmlvl 
  ------------------------- 
  1       2      1 
  1       3      1 
  1       4      0 
  4       17     1 
  4       18     1 
  4       19     1 
  1       5      1 
  1       6      1 
  1       7      1 
  1       8      0 
  8       12     1 
  8       10     1 
  8       11     1 
  8       13     1 
  8       14     1 
  8       15     1 
  8       16     1 
  1       9      1 
  1       10     1 
  1       11     1 
  

Optional: Simplified Hierarchy Format (Without btmlvl) #

If you want to remove btmlvl and just show the parent-child chain cleanly, you can adjust the final SELECT to exclude btmlvl—the sorted hierarchy path already ensures the correct order. If you want to add visual indentation for clarity (to make the nested structure easier to read), you can use REPLICATE to add spaces based on the depth of the part:

WITH bom (bomItem, partId, btmlvl, hierarchy_path, depth) AS (
    SELECT 
        [bomItem], 
        [partId], 
        [btmlvl],
        CAST([bomItem] AS VARCHAR(MAX)) AS hierarchy_path,
        0 AS depth -- Root level is depth 0
    FROM [TESTDB].[dbo].[BOMTABLE] 
    WHERE [bomItem] = 'PART# GOES HERE, PASSED IN BY THE APP'

    UNION ALL

    SELECT 
        subQuery.bomItem, 
        subQuery.partId, 
        subQuery.btmlvl,
        mainQuery.hierarchy_path + '>' + CAST(subQuery.partId AS VARCHAR(MAX)),
        mainQuery.depth + 1 -- Increment depth for child nodes
    FROM [TESTDB].[dbo].[BOMTABLE] AS subQuery
    INNER JOIN bom AS mainQuery 
        ON subQuery.bomItem = mainQuery.partId
)
SELECT 
    bomItem,
    -- Add indentation to partId based on depth
    REPLICATE('  ', depth) + partId AS partId
FROM bom
ORDER BY hierarchy_path;

Sample Output with Indentation #

bomItem partId 
--------------- 
1       2 
1       3 
1       4 
4         17 
4         18 
4         19 
1       5 
1       6 
1       7 
1       8 
8         12 
8         10 
8         11 
8         13 
8         14 
8         15 
8         16 
1       9 
1       10 
1       11 

Note: You don't need PIVOT here—PIVOT is used for rotating rows into columns, which isn't necessary for this hierarchical display. The hierarchy path sorting is the key to getting the order right, and optional indentation just makes the structure more readable.

内容的提问来源于stack exchange，提问作者jh2399