question

Here's one method, but it uses a correlated subquery, so performance might be...suboptimal. declare @t table (id int, invoice_id int, product_id int, invoice_line_id_link int); insert into @t values (53,10,383,0), (54,10,344,53), (55,10,920,53), (57,10,384,0), (58,10,359,57), (59,10,242,57), (60,10,284,0); select t1.id, t1.invoice_id, t1.product_id, t1.invoice_line_id_link, case when invoice_line_id_link = 0 then product_id ELSE (select t2.product_id FROM @t t2 WHERE t2.id = t1.invoice_line_id_link) end AS desired_output from @t t1

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): select a.*, isnull(b.ParentItemCode, a.itemcode) ParentItemCode from dbo.sampletable a cross apply ( select min(itemcode) from dbo.sampletable where invoice_id = a.invoice_id and invoice_line_id = a.invoice_line_id_link ) b(ParentItemCode) order by invoice_id, invoice_line_id; Here are the abridged results from this query based on the sample data in question: invoice_id invoice_line_id invoice_line_id_link itemcode ParentItemCode ----------- --------------- -------------------- ---------- -------------- 2 3 0 BC-105801 BC-105801 2 3 0 BC-105801 BC-105801 2 3 0 BC-105801 BC-105801 2 3 0 BC-105801 BC-105801 2 4 3 BC-103990 BC-105801 2 4 3 BC-103990 BC-105801 2 4 3 BC-103990 BC-105801 2 4 3 BC-103990 BC-105801 -- -- -- --------- --------- 2 8 3 BC-100971 BC-105801 2 8 3 BC-100971 BC-105801 2 8 3 BC-100971 BC-105801 2 8 3 BC-100971 BC-105801 2 9 0 BC-101222 BC-101222 2 9 0 BC-101222 BC-101222 2 9 0 BC-101222 BC-101222 2 9 0 BC-101222 BC-101222 2 10 9 BC-102093 BC-101222 2 10 9 BC-102093 BC-101222 2 10 9 BC-102093 BC-101222 2 10 9 BC-102093 BC-101222 2 11 9 BC-101159 BC-101222 2 11 9 BC-101159 BC-101222 2 11 9 BC-101159 BC-101222 2 11 9 BC-101159 BC-101222 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. Oleg