if object_id('tempdb..#TempSeason') is not null

    drop table #TempSeason --29

create table #TempSeason (row  int ,itemnumber  int ,id nchar (10),order_id nvarchar (100),material nvarchar (50), billto_id nvarchar (100),shipto_id nvarchar (50),shipped_quantity int,materialhidden nvarchar (50))
insert into #TempSeason 
select DISTINCT 1 row
,0  AS itemnumber 
,'H' AS ID
,o.order_id as order_id
,'' as Material
,shipto.billto_id as billto_id
,shipto.shipto_id shipto_id 
,'' AS shipped_quantity
,''  as materialhidden
from orderitem
inner join [order] o on o.order_id=orderitem.order_id
inner join shipto on shipto.shipto_id=o.shipto_id
 

insert into #TempSeason 
select DISTINCT 2 row
,ROW_NUMBER() OVER (ORDER BY o.order_id) *10 AS itemnumber 
,'D'
,o.order_id as order_id
,orderitem_style_code+'-'+orderitem_color_code   AS Material
,'' as billto_id
,'' shipto_id 
,'' AS shipped_quantity
,orderitem_style_code+'-'+orderitem_color_code  as materialhidden
from orderitem
inner join [order] o on o.order_id=orderitem.order_id
inner join shipto on shipto.shipto_id=o.shipto_id
 
insert into #TempSeason 
select DISTINCT 3 row
,ROW_NUMBER() OVER (ORDER BY o.order_id) *10 AS itemnumber 
,'D1'
,o.order_id as order_id
,''   AS Material
,'' as billto_id
,'' shipto_id 
,shipped_quantity AS shipped_quantity
,orderitem_style_code+'-'+orderitem_color_code  as materialhidden
from orderitem
inner join [order] o on o.order_id=orderitem.order_id
inner join shipto on shipto.shipto_id=o.shipto_id
 
select distinct row   ,itemnumber   ,id ,order_id ,material, billto_id 
,shipto_id,shipped_quantity ,materialhidden from #TempSeason 
ORDER BY ORDER_ID,ROW
 

declare @tbl as table(row int,id varchar(10),orderid int)
insert into @tbl
select 1,'H',1005 union all
select 2,'D',1005 union all
select 3,'D1',1005 union all
select 1,'H',1007 union all
select 2,'D',1007 union all
select 2,'D',1007 union all
select 2,'D',1007 union all
select 1,'H',1015 union all
select 2,'D',1015 union all
select 3,'D1',1015 

select row as oldrow,id,orderid,case when id='H' then 0 else newrow end as newrow from
(
select *,row_number()over (partition by  case when id='D' then orderid when id='H'  then 0 end,orderid order by id,orderid)*10 as newrow from @tbl

)t order by orderid,row

quote:

---

Originally posted by collie

Yea this seems to be exactly what i want. I will try implement it on my query.

Thanks a lot :)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

---