Here is a quick sample solution, much worse performance wise than Chris's but I put it forward more as an alternative method anyway.

😎

USE tempdb;

GO

;WITH TDOC(

[ID]

,[Customer name]

,[Customer address]

,[Product number]

,[Product name]

,[Quantity]

,[Price]

,[Product number2]

,[Product name2]

,[Quantity2]

,[Price2]

)

AS

(

SELECT

[ID]

,[Customer name]

,[Customer address]

,[Product number]

,[Product name]

,[Quantity]

,[Price]

,[Product number2]

,[Product name2]

,[Quantity2]

,[Price2]

FROM (VALUES

(2, 'Andy' ,'Andy''s way 2',24,'Glue',3,35,39,'Oyster',2,9)

,(3, 'John' ,'Dandy''s way 4',15,'Slime',2,10,17,'Clan' ,4,12)

) AS X([ID]

,[Customer name]

,[Customer address]

,[Product number]

,[Product name]

,[Quantity]

,[Price]

,[Product number2]

,[Product name2]

,[Quantity2]

,[Price2])

)

,CUSTOMER AS

(

SELECT

T.ID AS COL_1

,T.[Customer name] AS COL_2

,T.[Customer address] AS COL_3

,NULL AS COL_4

,NULL AS COL_5

FROM TDOC T

)

,PRODUCT_1 AS

(

SELECT

T.ID

,CONVERT(VARCHAR(25),T.[Product number],1) AS [Product number]

,T.[Product name]

,T.[Quantity]

,T.[Price]

FROM TDOC T

)

,PRODUCT_2 AS

(

SELECT

T.ID

,CONVERT(VARCHAR(25),T.[Product number2],1) AS [Product number]

,T.[Product name2]

,T.[Quantity2]

,T.[Price2]

FROM TDOC T

)

SELECT

*

FROM CUSTOMER C

UNION ALL

SELECT

*

FROM PRODUCT_1

UNION ALL

SELECT

*

FROM PRODUCT_2