Here's one method, but it uses a correlated subquery, so performance might be...suboptimal.
answered Jan 12 at 04:07 PM
You can take the query from @ThomasRushton ♦♦ answer and just slightly restate it so that the "Subquery returned more than 1 value" error is not triggered. The reason that the error triggered is due to the fact that there are multiple records for the same invoice, invoice line and invoice line id link combination. Worse still, there seems to be impossible situation when there are multiple invoice_line_id for the same store, site, terminal, status where invoice_line_id_link is equal to zero. For example, rows 9 through 32 in the sample data are all for invoice_id = 2. All these rows should point to row 9 for their respective parent item, so the rows with invoice line id link 3 through 8 feature item code BC-105801 as their parent item. This makes perfect sense but then come in the rows # 33 through 36 with the same invoice_id = 2 but their invoice line id link is zero again even though it is the same invoice (?!?). This means that rows 33 - 36 have a different parent (BC-101222) and the rows 37 - 44 all have the same parent which is different from the rows 9 - 32 for the same invoice.
While it is not possible to understand the reason for this strange behaviour without knowing the details about the underlying system, it is possible to restate the query from @ThomasRushton ♦♦ answer while preserving its logic . Here is the script which will produce desired result (please have a look at the values in the rightmost column named ParentItemCode):
Here are the abridged results from this query based on the sample data in question:
By the way, there is no reason whatsoever to use a cursor here. I am also puzzled about why there is a question about possibility of using a cursor when the quest is to find a "SQL Server optimized" solution. Cursors have their place in T-SQL, otherwise, they would not exist, but they should be used only for some procedural tasks when the set based solutions are not suitable. When it comes to the data selection tasks, the set based solutions are the way to go.
Hope this helps.
answered Jan 18 at 03:44 PM