How to Combine?

Other SQL Server Topics
1

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
Combine

2

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

image

3

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