Q:

Combining 2 rows into 1 by using self join

Following simplified table
CustNr OrderNr Date    Price Curry
1      555     030316  2,4   EUR
1      666     030316  2,5   EUR
1      777     030316  2,3   EUR
1      777     030316  1,9   USD
1      888     030316  2,3   EUR
1      888     030316  2,4   EUR

Desired output:
CustNr OrderNr Date    Price Curry   CustNr OrderNr Date    Price Curry
1      555     030316  2,4   EUR
1      666     030316  2,5   EUR
1      777     030316  2.3   EUR     1      777     030316   1,9   USD
1      888     030316  2,3   EUR     1      888     030316   2,4   EUR

I tried following self join:
SELECT * FROM TEST T1 INNER JOIN TEST T2 ON T1.OrderNr = T2.OrderNr

But then i get duplicate records and a GROUP BY only works when grouping by OrderNr but I also need the other columns aswell. 

A:

Need to number rows with the same OrderNr some way.
with tn as (
    select *, rn=row_number() over(partition by OrderNr order by Price)
    from table
)
select t1.*, t2.*
from tn t1
left join tn t2 on t2.OrderNr = t1.OrderNr and t2.rn=2
where t1.rn=1

A:

I feel like your question is not 100% clear.  But based on your current description, this query should work:
with cte as (
  select CustNr, OrderNr, Date, Price, Curry,
         row_number() over (partition by OrderNr order by OrderNr) as rn
    from test
)
select t1.CustNr, t1.OrderNr, t1.Date, t1.Price, t1.Curry,
       t2.CustNr, t2.OrderNr, t2.Date, t2.Price, t2.Curry
  from cte t1
  left join cte t2
    on t2.OrderNr = t1.OrderNr
   and t2.rn = 2
 where t1.rn = 1

With the above query, which rows appear on the left vs. right hand side is completely arbitrary.  If you want to define which row goes where, you can do that by adjusting the order by clause in the row_number window function.

