How to Combine?

Hi,

I have the attached scenario. I need your help.

Product A cost \$1 and was Replaced with C which cost \$3.
Product B cost \$2 and was Replaced with C which cost \$4.

I want results like the ones below.
|Product#|Cost|
|A| \$1.00 |
|B| \$2.00 |
|C| \$3.00 |
|D| \$4.00 |

Thank You,
Yoga

hi

hope this helps

one issue is the Row Number i create uses order by Product#
which may not give the correct result depending on how your data is

you can create a identity column

create data script

declare @Products table ( ProductNo varchar(1) , Cost int)
insert into @Products select 'A' ,1
insert into @Products select 'B' ,2
insert into @Products select 'C' ,3
insert into @Products select 'D' ,4

``````; with cte as
(
select ROW_NUMBER() over(order by ProductNo) as rn , * from @Products
)
select  a.ProductNo
, a.Cost
, b.ProductNo
, B.Cost
from cte a join cte b on a.rn+2 = b.rn
``````

This will avoid the overhead of an extra scan of the table (and of the subsequent join of the two scans):

``````;WITH cte_products AS (
SELECT *, ROW_NUMBER() OVER(ORDER BY ProductNo) + 1 AS row_num
FROM @Products
)
SELECT
MAX(CASE WHEN row_num % 2 = 0 THEN ProductNo ELSE NULL END) AS Product#,
MAX(CASE WHEN row_num % 2 = 0 THEN Cost ELSE NULL END) AS Cost,
MAX(CASE WHEN row_num % 2 = 1 THEN ProductNo ELSE NULL END) AS Replacement_Product#,
MAX(CASE WHEN row_num % 2 = 1 THEN Cost ELSE NULL END) AS Cost
FROM cte_products
GROUP BY row_num / 2
``````