Mance Mance - 3 months ago 6
SQL Question

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.

Answer

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.