Quick and simple

😎

USE tempdb;

GO

/* LETS PRETEND THIS IS THE TABLE */

;WITH BASE_DATA AS

(

SELECT

ItemId

,CustomerItemId

,Customer

,Plant

,UnitPrice

,CustomerPO

FROM (VALUES

(637,'82604029-5','C1','P1','0.7208','P1000')

,(637,'82604029-5','C2','P1','0.6889','441670')

,(637,'82604029-5','C2','P1','0.6889','411754')

,(637,'82604029-5','C2','P1','0.6889','412530')

) AS X(ItemId,CustomerItemId,Customer,Plant,UnitPrice,CustomerPO)

)

/* AND THIS IS OUR CTE */

,RN_DATA AS

(

SELECT

ROW_NUMBER() OVER (PARTITION BY ItemId ORDER BY (SELECT NULL)) AS BD_RID

,BD.ItemId

,BD.CustomerItemId

,BD.Customer

,BD.Plant

,BD.UnitPrice

,BD.CustomerPO

FROM BASE_DATA BD

)

SELECT

RD1.ItemId

,RD1.CustomerItemId

,RD1.Customer

,RD1.Plant

,RD1.UnitPrice

,RD1.CustomerPO

,RD2.CustomerItemId

,RD2.Customer

,RD2.Plant

,RD2.UnitPrice

,RD2.CustomerPO

,RD3.CustomerItemId

,RD3.Customer

,RD3.Plant

,RD3.UnitPrice

,RD3.CustomerPO

,RD4.CustomerItemId

,RD4.Customer

,RD4.Plant

,RD4.UnitPrice

,RD4.CustomerPO

FROM RN_DATA RD1

OUTER APPLY RN_DATA RD2

OUTER APPLY RN_DATA RD3

OUTER APPLY RN_DATA RD4

WHERE RD1.ItemId = RD2.ItemId

AND RD2.ItemId = RD3.ItemId

AND RD3.ItemId = RD4.ItemId

AND RD1.BD_RID = RD2.BD_RID - 1

AND RD2.BD_RID = RD3.BD_RID - 1

AND RD3.BD_RID = RD4.BD_RID - 1

Results

ItemId CustomerItemId Customer Plant UnitPrice CustomerPO CustomerItemId Customer Plant UnitPrice CustomerPO CustomerItemId Customer Plant UnitPrice CustomerPO CustomerItemId Customer Plant UnitPrice CustomerPO

----------- -------------- -------- ----- --------- ---------- -------------- -------- ----- --------- ---------- -------------- -------- ----- --------- ---------- -------------- -------- ----- --------- ----------

637 82604029-5 C1 P1 0.7208 P1000 82604029-5 C2 P1 0.6889 441670 82604029-5 C2 P1 0.6889 411754 82604029-5 C2 P1 0.6889 412530