I have a table called Materials (SQL Server 2012). In that table there is a field called PartNo and one called SubPartNo. If a SubPartNo has its own SubPartNo then it will be listed further down the table. For example, PartNo 555-123 has a SubPartNo of 111-222. Part 111-222 also has its own sub parts so 111-222 is listed in the PartNo column. This will continue until all the sub parts have been listed.

PartNo	SubPartNo	Description	ItemNo
555-123	111-222	Motor Assembly	1
555-123	121-333	Bearing Shaft	2
555-123	131-444	Coupler	3
555-123	141-555	Bearing Wheel	4
111-222	232-999	Motor	5
111-222	232-888	Motor Mount	6
232-999	751-012	Motor Shaft	7
232-999	761-099	Motor Housing	8

I need the report to look like this...

555-123
	111-222	Motor Assembly
		232-999	Motor Shaft
		232-888	Motor Mount
	121-333	Bearing Shaft
	131-444	Coupler
	…
	…

Basically, while compiling the report I need Crystal Reports to...

1. "Print" the current record.
2. Check if the SubPartNo in the current record is listed somewhere else in the PartNo field.
1. If so, go to that record and print it
1. Return to Step 2.
2. If not, move to next PartNo.
1. Return to Step 1.

Thanks to Dblank I can figure out how to indent the records but I can't figure out how to get Crystal Reports to follow the logic while its compiling the report.

Any help would be greatly appreciated.

Edited by highrise955 - 13 Feb 2017 at 4:07am

Thanks for the suggestion.

I can only get it to show the second level SubPart. Any suggestions on how to drill down further. (i.e. Show the sub parts of the sub parts...)

I tried several ways of doing it. Here is an example of one of them...

 


Edited by highrise955 - 14 Feb 2017 at 1:21am

Yes, it has all the [PartNo] values but does not contain the [SubPartNo] column.

I'll look into that.

I also tried to do subqueries...

SELECT [PartNo], [SubPartNo]
FROM Materials
WHERE [PartNo] IN (
    SELECT [SubPartNo]
    FROM Materials
    WHERE [PartNo] IN (
        SELECT [SubPartNo]
        FROM Materials
        WHERE [PartNo] IN (
            SELECT [SubPartNo]
            FROM Materials
            WHERE [PartNo] LIKE '4535-670-62391')))

Each SELECT statement appears to grab the next level of SubPartNo's. This particular assembly has 5 levels so this amount of subqueries does the trick. Now I am trying to figure out how to grab/save the records after each subquery so I can build a table or view or something so I can construct a report.

Any suggestions?