SQL Unpivot with Additional variable field creation

Question

I have created the following query:

SELECT UnPivotTable.*
FROM (  SELECT
             "Stage 1" as "Stage 1 Date",
             "Stage 2" as "Stage 2 Date",
             "Stage 3" as "Stage 3 Date",
             "Stage 4" as "Stage 4 Date",
             "Proposal Number",
             "Amount"
    FROM  "Proposals" 
) AS  Temp
unpivot
("Proposals" FOR "Stage"  IN ( Temp."Stage 1 Date"  , Temp."Stage 2 Date"  , Temp."Stage 3 Date"  , Temp."Stage 4 Date"  )
) AS UnPivotTable 

This as a query itself works fine. So far so good

This works fine, BUT I need the Amount field vary depending on the Stage. So if the Stage is "Stage 1" then the Amount should be Amount *0.1, for "Stage 2" it should be Amount * 0.3, for "Stage 3" it should be Amount * 0.4, and for "Stage 4" it should be Amount * 0.2.

I can't quite see a way to do this within this SQL statement. I guess I could use a secondary table somehow but it would be nice to keep this all in a single SQL statement

Answer 1

As is often the case, PIVOT and UNPIVOT break down when it gets even midly complicated.

You are better off doing a manual unpivot using CROSS APPLY (VALUES

SELECT
  p.[Proposal Number],
  v.Amount,
  v.Stage,
  v.Proposal
FROM Proposals p
CROSS APPLY (VALUES
    ('Stage 1 Date', p.[Stage 1], p.Amount * 0.1),
    ('Stage 2 Date', p.[Stage 2], p.Amount * 0.3),
    ('Stage 3 Date', p.[Stage 3], p.Amount * 0.4),
    ('Stage 4 Date', p.[Stage 4], p.Amount * 0.2)
) v(Stage, Proposals, Amount);

Do yourself a favour and give your columns names that don't need quoting.