Separate data from 1 column into multiple columns

Question

Below is sample data and expected results:

Table1: salesprice

ItemNo Variant SalesCode UnitPrice
 A 1 SRP 100
 A 2 SRP 200
 A 1 WSP 50
 A 2 WSP 100
 B 1 SRP 300
 B 2 SRP 400
 B 1 WSP 150
 B 2 WSP 200

Query Results:

ItemNo Variant SalesCode(SRP) UnitPrice SalesCode(WSP) UnitPrice
 A 1 SRP 100 WSP 50 
 A 2 SRP 200 WSP 100
 B 1 SRP 300 WSP 150
 B 2 SRP 400 WSP 200

Answer 1

CREATE TABLE #Data
 (
 ItemNo VARCHAR(10),
 Variant INT,
 SalesCode VARCHAR(30),
 UnitPrice INT
 )
GO
INSERT INTO #Data
 ( ItemNo, Variant, SalesCode, UnitPrice )
VALUES ( 'A', 1, 'SRP', 100 ),
 ( 'A', 2, 'SRP', 200 ),
 ( 'A', 1, 'WSP', 50 ),
 ( 'A', 2, 'WSP', 100 ),
 ( 'B', 1, 'SRP', 300 ),
 ( 'B', 2, 'SRP', 400 ),
 ( 'B', 1, 'WSP', 150 ),
 ( 'B', 2, 'WSP', 200 )
GO
SELECT ItemNo, Variant, 'SRP' AS SalesCode_SRP,
 SUM(CASE SalesCode
 WHEN 'SRP' THEN UnitPrice
 ELSE 0
 END) UnitPrice_SRP, 'WSP' AS SalesCode_WSP,
 SUM(CASE SalesCode
 WHEN 'WSP' THEN UnitPrice
 ELSE 0
 END) UnitPrice_WSP
FROM #Data
GROUP BY ItemNo, Variant 
GO
DROP TABLE #Data

• James Anderson: Just needs an ORDER BY ItemNo, Variant to exactly match the desired output

Answer 2

select d1.*, d2.SalesCode, d2.UnitPrice 
from Data d1, Data d2 
where d1.Variant = d2.Variant and d1.ItemNo = d2.ItemNo 
 and d1.SalesCode = 'SRP' 
 and d2.SalesCode = 'WSP';