Rearranging SQL Query Results

Question

I am working on an application that queries a SQL database to get Billing of Material information. I have successfully queried out the data that I wanted using the following query.

WITH bom (bomItem, partId, btmlvl)
AS
(
    SELECT [bomItem], [partId], [btmlvl]
    FROM [TESTDB].[dbo].[BOMTABLE]
    WHERE [TESTDB].[dbo].[BOMTABLE].[bomItem] = 'PART# GOES HERE, PASSED IN BY THE APP'
    UNION ALL
    SELECT subQuery.bomItem, subQuery.partId, subQuery.btmlvl
    FROM [TESTDB].[dbo].[BOMTABLE] AS subQuery
    INNER JOIN bom AS mainQuery
    on subQuery.bomItem = mainQuery.partId
)

SELECT [bomItem], [partId], [btmlvl]
FROM bom

The query results in (Sample Data):

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

The data works as follows:

bomItem - The part number of the assembly that I am looking up the bill of materials for

partId - All of the parts and sub-assemblies tied to the bomItem I'm looking up

btmlvl - 0 indicates it's a sub-assembly, 1 indicates a standalone part (not really important, I was just using this to make sure I was getting the results that I wanted)

And what I want it to look like is (Expected Results):

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

I could export it to a CSV or something else and write a script to rearrange the data, but I would prefer if it could be done as part of the SQL query. I messed around some with CASE statements but didn't quite achieve the desired results.

Bonus points if it could be aligned as follows (again the btmlvl isn't important, and will ultimately be removed). Again, novice SQL user here, but I think this would be possible using a PIVOT? (I just haven't gotten that far):

bomItem      partId      partId2
---------------------------------
...
1            8            
             8            12
             8            10
             8            11
             8            13
             8            14
             8            15
             8            16
...           

Any help would be greatly appreciated, and please excuse the lengthiness of this post!

EDIT:

The only difference between my actual data and what I supplied is I simplified the part numbers from 11 characters strings to one-digit and two-digit integers and also removed other information I thought was irrelevant (e.g. qty on hand, purchasing cost, etc.). The first set of data is the sample data resulting from passing in "1" to WHERE [TESTDB].[dbo].[BOMTABLE].[bomItem] = 'PART# GOES HERE, PASSED IN BY THE APP'. This means that the first 10 lines of that data (again, anything with the bomItem == "1"), are all of the parts that make up bomItem == "1".

Looking at the "btmlvl" column for all bomItem == "1", you can see partId's 4 & 8 are assemblies (read sub-assemblies of bomItem == "1"), because btmlvl == "0". This means I need to perform additional queries to obtain the bill of materials for those part numbers. This is where the subQuery within the CTE comes in, giving me the line items starting with bomItem == "4" and bomItem == "8".

The difference between the output as it stands right now, and the output that is desired is the current output lists all parts for bomItem == "1", then all parts for bomItem == "8", and lastly all parts for bomItem == "4".

But what I want is where bomItem == "1" & partId's 1 through 3 to appear as they are right now, but once partId == "4", directly underneath that line I want all of the lines where bomItem == "4", then continue with the lines that contain bomItem == "1" & partId's 5, 6, 7, but again once partId 8 is reached, do a similar thing as with partId 4, then finish partId's 9, 10, 11 as normal.

Answer 1

This is inherently hierarchal data, so you need a self-join.

I think this gets you what you want.

SELECT a.* FROM bom a
LEFT JOIN bom b
ON a.bomitem = b.partID
ORDER BY ISNULL (b.partID, a.partID), a.partID