CODEWITHSUNDEEP
sqlsql-serverjoin
Mance
Mance

Reputation: 79

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.

Upvotes: 0

Views: 116

Answers (5)

varun kumar dutta
varun kumar dutta

Reputation: 202

You can try this one too:

DECLARE @tab TABLE (
  CustNr int,
  OrderNr int,
  Date int,
  Price varchar(20),
  Curry char(3)
);
INSERT INTO @tab
  VALUES (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');

SELECT
  T1.custNr,
  T1.OrderNr,
  T1.date,
  T1.price,
  T1.curry,
  T2.custNr,
  T2.OrderNr,
  T2.date,
  T2.price,
  T2.curry
FROM (SELECT
  T1.custNr,
  T1.OrderNr,
  T1.date,
  T1.price,
  T1.curry,
  ROW_NUMBER() OVER (PARTITION BY T1.custNr,
  T1.OrderNr ORDER BY T1.custNr,
  T1.OrderNr) AS [rank1]
FROM @tab T1) T1
LEFT JOIN (SELECT
  (custNr) AS custNr,
  OrderNr,
  (date) AS date,
  (price) AS price,
  (curry) AS curry,
  ROW_NUMBER() OVER (PARTITION BY custNr,
  OrderNr ORDER BY custNr,
  OrderNr) AS [rank]
FROM @tab T2) T2
  ON T1.OrderNr = T2.OrderNr
  AND T2.rank > 1
WHERE T1.rank1 <> 2
ORDER BY T1.OrderNr;

Upvotes: 0

sstan
sstan

Reputation: 36473

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.

Upvotes: 3

Serg
Serg

Reputation: 22811

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

Upvotes: 4

ScaisEdge
ScaisEdge

Reputation: 133360

You should use left join instead of inner join and filter by Curry

SELECT * FROM TEST T1 LEFT JOIN TEST T2 ON (T1.OrderNr = T2.OrderNr
AND  T1.Curry <> T2.Curry )

Upvotes: 1

BlackjacketMack
BlackjacketMack

Reputation: 5692

You need a top-level query that represents unique order numbers:

select 
t.OrderNr,
t1.*,
t2.*
from (select distinct OrderNr from test) t
cross apply (select top 1 * from test t1 where t1.OrderNr = t.OrderNr order by Curry) t1
outer apply (select top 1 * from test t2 where t2.OrderNr = t.OrderNr and t2.Curry <> t1.Curry order by Curry) t2

I think this answers the question, but I think it raises more questions about the design of the desired output.

Upvotes: 0

Related Questions