ItemNumber	DisplaySeqNum
10	1
  	2
  	3
	4
20	5
	6
	7
	8
30	9
	10
	11

ItemNumber	DisplaySeqNum
10	1
10	2
10	3
10	4
20	5
20	6
20	7
20	8
30	9
30	10
30	11

if object_id('tempdb.dbo.#TempTable') is not null
	drop table #TempTable

create table #TempTable(ItemNumber varchar(10) not null, DisplaySeqNum int not null)


insert into #TempTable
values('10',1),('',2),('',3),('',4),('20',5),('',6),('',7),('',8),('30',9),('',10),('',11)

select *
from #TempTable

;with s1 as (
		select scqli.ItemNumber
				,scqli.DisplaySeqNum
		from #TempTable scqli
		),
		s2 as (
				select ItemNumber
						,DisplaySeqNum
						,ROW_NUMBER()over(order by ItemNumber) RowNum
				from s1
				where ItemNumber <> ''
				),
				s3 as (
						select sd.ItemNumber
								,sc.DisplaySeqNum
						from s1 sc
						inner join (
									select sa.ItemNumber
											,sa.DisplaySeqNum StartLine
											,isnull(sb.DisplaySeqNum-1,100000) EndLine
									from s2 sa
									left join (select ItemNumber, DisplaySeqNum, RowNum - 1 RowNum from s2) sb
									on sa.RowNum = sb.RowNum
									) sd
						on sc.DisplaySeqNum between sd.StartLine and sd.EndLine
						)

select *
from s3

Want a cool sig (11/12/2012)

---

I have a recordset that returns the following data:

ItemNumber	DisplaySeqNum
10	1
  	2
  	3
	4
20	5
	6
	7
	8
30	9
	10
	11

It's like an invoice sheet where there's multiple parts to each item number. There's no other relationship between the parts and the item number other then the fact that when ordered by the display sequence the item number that's first applies to all parts that do not have an item number until the next item number is filled. (programmers). Anyways I've worked out a solution using CTE but wanted to know if there's a better way to write it. I'm avoiding cursors or other loops. The result is shown below. Potentially there could be hundreds maybe thousands of parts and performance may be an issue. Thanks in advance to anyone who replies.

ItemNumber	DisplaySeqNum
10	1
10	2
10	3
10	4
20	5
20	6
20	7
20	8
30	9
30	10
30	11

if object_id('tempdb.dbo.#TempTable') is not null
	drop table #TempTable

create table #TempTable(ItemNumber varchar(10) not null, DisplaySeqNum int not null)


insert into #TempTable
values('10',1),('',2),('',3),('',4),('20',5),('',6),('',7),('',8),('30',9),('',10),('',11)

select *
from #TempTable

;with s1 as (
		select scqli.ItemNumber
				,scqli.DisplaySeqNum
		from #TempTable scqli
		),
		s2 as (
				select ItemNumber
						,DisplaySeqNum
						,ROW_NUMBER()over(order by ItemNumber) RowNum
				from s1
				where ItemNumber <> ''
				),
				s3 as (
						select sd.ItemNumber
								,sc.DisplaySeqNum
						from s1 sc
						inner join (
									select sa.ItemNumber
											,sa.DisplaySeqNum StartLine
											,isnull(sb.DisplaySeqNum-1,100000) EndLine
									from s2 sa
									left join (select ItemNumber, DisplaySeqNum, RowNum - 1 RowNum from s2) sb
									on sa.RowNum = sb.RowNum
									) sd
						on sc.DisplaySeqNum between sd.StartLine and sd.EndLine
						)

select *
from s3

 UPDATE tt
    SET ItemNumber = ca.ItemNumber
   FROM #TempTable tt
 CROSS APPLY (SELECT TOP 1 ItemNumber 
                FROM #TempTable ttca 
               WHERE ttca.DisplaySeqNum <= tt.DisplaySeqNum 
                 AND ttca.ItemNumber > '' 
               ORDER BY ttca.DisplaySeqNum DESC) ca
;