-- Prepare some sample data to code against
DROP TABLE #Sample
CREATE TABLE #Sample (
ID INT,
[Customer name] VARCHAR(20),
[Customer address] VARCHAR(20),
[Something] INT,
[Product number] INT,
[Product name] VARCHAR(20),
Quantity VARCHAR(20),
Price VARCHAR(20),
[Product number2] VARCHAR(20),
[Product name2] VARCHAR(20),
Quantity2 VARCHAR(20),
Price2 INT
)
INSERT INTO #Sample (ID,[Customer name],[Customer address],[Something],
[Product number],[Product name],Quantity,Price,[Product number2],[Product name2],Quantity2,Price2)
SELECT
2, 'Andy', 'Andy''s way', 2,
24, 'Glue', 3, 35,
39, 'Oyster', 2, 9
-- First try at a solution.
-- It's not perfect: there's some confusion about the first four columns.
-- I think there should be two product lines, each for customer 'Andy'.
-- You decide.
SELECT
x.[Product number], x.[Product name], x.Quantity, x.Price
FROM #Sample s
CROSS APPLY (VALUES
(ID, [Customer name], [Customer address], [Something]),
([Product number], [Product name], Quantity, Price),
([Product number2], [Product name2], Quantity2, Price2)
) x ([Product number], [Product name], Quantity, Price)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden