What do you mean? cte is part of the query (you can think of it as a derived table or macro). So, it's a single query.

>Hi Naomi
>
>thanks for your reply. Seem that is actually what i need
>
>please excuse my ignorance
>
>should i set it as a single statement or two steps?
>
>>Try
>>

>>;with cte as (select *, row_number() over (partition by Reference order by [Order]) as Rn from myTable)
>>
>>select Reference, 
>>min(case when Rn = 1 then [Order] end) as Order1,
>>min(case when Rn = 2 then [Order] end) as Order2,
>>min(case when Rn = 3 then [Order] end) as Order3,
>>
>>min([Date]) as [Min Date],
>>max([Date]) as [Max Date],
>>min(Type) as [Type],
>>from cte
>>GROUP BY Reference

>>
>>>Hi guys
>>>
>>>I actually do not know if this is feasible or not. My first thought is that it cannot be done with a simple query. But may be you guys have had experience on complex query statements
>>>
>>> I have a table or a result set that looks kind a like this
>>>
>>>Reference Order BillTo Date Type
>>>1 780001 Mov01 01-Nov Local
>>>1 780002 Mov02 02-Nov Local
>>>1 780003 Mov03 13-Nov Local
>>>2 780004 Mov04 04-Nov Local
>>>2 780005 Mov05 05-Nov Local
>>>2 780006 Mov06 06-Nov Local
>>>3 780007 Mov07 07-Nov Local
>>>3 780008 Mov08 08-Nov Local
>>>4 780009 Mov09 09-Nov Local
>>>4 780010 Mov10 10-Nov Local
>>>4 780011 Mov11 11-Nov Local
>>>5 780012 Mov12 11-Nov Export
>>>5 780013 Mov13 13-Nov Export
>>>5 780014 Mov14 12-Dec Export
>>>6 780015 Mov15 15-Nov Local
>>>6 780016 Mov16 16-Nov Local
>>>6 780017 Mov17 17-Nov Local
>>>7 780018 Mov18 18-Nov Local
>>>7 780019 Mov19 19-Nov Local
>>>8 780020 Mov20 20-Nov Local
>>>8 780021 Mov21 21-Nov Local
>>>8 780022 Mov22 22-Nov Local
>>>9 780023 Mov23 23-Nov Local
>>>9 780024 Mov24 24-Nov Local
>>>9 780025 Mov25 25-Nov Local
>>>
>>>
>>>What i need is to have something like this
>>>
>>>

>>>Reference	Order 1	Order 2	Order 3	Min Date	Max Date	Type	Exp Date	 Demurage 
>>>1	         780001	780002	780003	01-Nov	13-Nov	Local	 07-Nov	 6 
>>>2	         780004	780005	780006	04-Nov	06-Nov	Local	 10-Nov	 -   
>>>3	         780007	780008		        07-Nov	08-Nov	Local	 13-Nov	 -   
>>>4	         780009	780010	780011	09-Nov	11-Nov	Local	 15-Nov	 -   
>>>5	         780012	780013	780014	11-Nov	12-Dec	Export 18-Nov	 24 
>>>6	         780015	780016	780017	15-Nov	17-Nov	Local	 21-Nov	 -   
>>>7	         780018	780019		        18-Nov	19-Nov	Local	 24-Nov	 -   
>>>8	         780020	780021	780022	20-Nov	22-Nov	Local	 26-Nov	 -   
>>>9	         780023	780024	780025	23-Nov	25-Nov	Local	 29-Nov	 -   
>>>

>>>
>>>
>>>i know this probably should be done with a snippet of code. But i want to make sure i cannot be dne by a complex query or a tool such as xtab.
>>>
>>>
>>>Pivot Table almost done it but the problem i have is that i can not add days to a Min(date) expression, it gives me a wrong data
>>>
>>>
>>>TIA