2

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
3
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
  • Just needs an ORDER BY ItemNo, Variant to exactly match the desired output – James Anderson Oct 16 '14 at 10:27
1
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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.