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
If it's not broken, fix it until it is.
My Blog